Aggregation by Month not working when changing date field

CBOI_GGriffin · November 30, 2020, 3:54pm

Hello everyone,

I'm having an odd problem with Dashboard Designer. I created a very simple line chart to display a sum of invoices by month. It works great as you can see below. The department wanted, instead, to see the same chart, but instead of using the "invoice date" they wanted to see it by "payment received date". Both are date only fields in a small sharepoint list with only 25 items. The ONLY thing I changed was the field as you can see by the screenshot, but when I change it from one date field to another, the results are very different and it doesn't seem to aggregate by month.

I'm sure I'm doing something wrong, but has anyone else run across this, or could help me? I've tried created a completely new chart and get the same results. I would think I could switch that field back and forth and it should be easy.

Working:

Not Working:

Thanks in advance,
Gary

jaitsujin · December 1, 2020, 1:17am

It should be easy as you've mentioned. Are you sure both fields are formatted the same way? are those calculated columns?
How about aggregation? Are you grouping?

The two Date columns, Inv Date and Payment Rec, are there blanks? or is there date for each of the 25 items.

CBOI_GGriffin · December 1, 2020, 7:01pm

Thanks for the response.

Both fields are formatted exactly the same. They are not calculated fields, and set as Short Date with Standard format. There is no grouping set for the chart. (see below).

The invoice date has an entry for every single record, however, the payment received date does not, as some payments haven't been received yet.

I literally just click the down arrow on the series field option and change from Invoice Date to Payment Received Date. I've also included a screenshot of the sharepoint list data.

Grouping Page:

Chart Settings:

Style Settings:

SharePoint List Data:

Thanks!
Gary

jaitsujin · December 1, 2020, 11:36pm

Got it. I'll try to recreate this shortly.

jaitsujin · December 2, 2020, 12:45am

Hi, So i was able to recreate the issue and indeed it seems blank dates are throwing this off.

When I used the Invoice Date it works and I get the following as expected.

Switching to the Payment Date created the same error you got.

To fix this you'll have to filter out blank dates. Either by creating a new view or simply plugging the filter into the CAML Query.

Use the following, but replace it with your internal column name.

<Where>
  <IsNotNull>
    <FieldRef Name="Payment_x0020_Rec_x0020_Date"/>
  </IsNotNull>
</Where>

And you should get the following as result.

As you can see only 4 of the 5 entries are showing now. i've added the data labels to highlight it better

Let me know if this works for you. Good luck!