Pivot Table Aggregate Summaries

I have a working dashboard that contains a pivot table. I have a control that allows user to summarize data by day,week,month,year. I also have begindate/enddate controls.

Date(Period) calc field logic is:
ifelse(
${PeriodStarting}=‘Day’,truncDate(“DD”,{line_item_usage_end_date}),
${PeriodStarting}=‘Month’,truncDate(“MM”,{line_item_usage_end_date}),
${PeriodStarting}=‘Quarter’,truncDate(“Q”,{line_item_usage_end_date}),
${PeriodStarting}=‘Week’,truncDate(“WK”,{line_item_usage_end_date}),
truncDate(“YYYY”,{line_item_usage_end_date}))

I am finding that everything works well EXCEPT if the begin date is not set to the first available date of the summarized period. For example, a begindate of 1/2/24 will display pivot table as follows:
Summarize by Day works correctly, displaying 1/2/24-enddate
Summarize by Week displays 1/7/24-enddate
Summarize by Quarter displays no data
Summarize by Year displays no data

I guess I was hoping that this logic would simply summarize based on the dates selected, even if a partial period exists. Any suggestions on how to handle partial periods in this setup?

Hi @jackattack6800,
please help me understand, you mentioned you have begindate/enddate controls. Are both controls available? Then where do we use the Date(Period) calculated field.

Thanks,
Prantika

Hopefully that attachment worked…

I have date picker controls at the top. They set parameters which set the between dates on the Date(Period) filter. I do include the start/end dates in filter setup. I also have the summarize by control which is a list of specific values that corresponds to periodstarting, which is referenced in Date(Period) calc field above.

Ok, I understand the problem.

So for Day, the effective range is 01-02-2024 to 03-10-2024
For Week, the effective range is 01-02-2024 to 03-10-2024 (since it is Sunday)
For Month, the effective range is 01-02-2024 to 03-01-2024 - this is also expected to return result
However for Quarter, the effective range looks like 01-02-2024 to 01-01-2024 , and logically the start date is later than end date, so it does not return a value .
Same applies to Year.

To be able to help further, can you help me understand the usecase? or if you are trying to capture year to date, month to date and similar metrics, then the filter should be Date(Period) to end Date , whereas now we are applying begin Date to Date(Period).

Hey @jackattack6800, I have built something very similar to this. Your Date(period) calculated field looks correct to me, and the visual looks awesome! Are your parameters ${BeginDate} and ${EndDate} filtering the new Date(period) calculated field you created OR filtering the attribute {line_item_usage_end_date}?

I would recommend filtering the attribute {line_item_usage_end_date}. This is what I do in my dashboard, and it allows me to group the filter date values under all of the summaries (including Quarter and Year).

1 Like

Perfect, that did it! Thank you!

1 Like