Collapsing pivot table shows null for YoY

[Krisztian_Nemeth]

Hi everyone,

I have the following pivot table:
2 row values: Group 1, Group 2
1 column: Quarter
Values: QoQ and YoY calculated fields
Filter: Date Hider “minOver(min(date), [date],POST_AGG_FILTER)” for last 4 quarters

When I collapse Group 1, it seems like my calculated fields stop working and the Date Hider doesn’t function anymore. I don’t get any results outside of the selected last 4 quarters (Quarter 4 still shows QoQ for example). Any suggestion on how I could fix this? I appreciate any feedback.

[neelay]

@Krisztian_Nemeth

These 2 articles will help you understand the level aware calculation in QS.

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

Level aware sum / sumOver problem

@Kristin @David_Wong I hope both of you get everything from this [image] BTW, all 4 pie charts are wrong, though 1st is showing the expected result, the other 3 are wrong as well as showing the unexpected result. We already went through sum(sumover()) example… So I didn’t include them in the screenshot.

most probably you will find your answer here… Let us know if you still have any questions.

[Wakana]

hi @Krisztian_Nemeth ,

Has the solution provided by @neelay helped you solve your question? if it does, please help us mark it as solution.

kind regards,
Wakana

[Krisztian_Nemeth]

@Wakana I think I know what the issue is. Since I collapse the main group it becomes aggregated and breaks the periodoverperiod calculation. Is there any way to go around this or use a different calculation?

This is how I calculate the QoQ:
periodOverPeriodDifference(%calculation,month,QUARTER,1)*10000

Thanks for all the help

Edit: Actually I have no clue to be fair. I just checked again and if I remove the date filter, it works. So something about the post_agg_filter doesn’t work when closing the pivot table.

[Krisztian_Nemeth]

I decided to change the SQL query to include YoY data, sometimes you can make the YoY with pivot table work but its very sensitive and would spend more time on fine tuning it vs just adding it to your query.

Also, adding date filters would make it even more difficult.

I couldn’t solve this problem in exclusively QS, however leave a comment if you have a better solution.