I have a pivot table where I want to calculate the Trailing 3 Month overall ctr (clicks / deliveries). The calculations work correctly in my dimension fields (tier), but not appearing at all for totals. I’ve tried using both windowSum() and lag() but the totals still don’t appear. I can’t use the field-well total option either since summing or averaging won’t give me the right number (the denominator is different). Looks like I’ll have to stick with ‘Default’. Any Advice? Below are my calculations.
Note: I’ve used both lag() and windowSum() and still doesn’t appear.
Hello @Forcxe, welcome to the QuickSight community!
First of all, I appreciate the thought you have put into these calculations and I have 100% found myself in a very similar situation. From what I can tell, you are pretty much just hitting at a QuickSight limitation.
I think the complication of running these comparison aggregations at the row level, while also referencing column values from previous date periods, and then dividing 2 different values running those same calculations basically just removes the ability for QuickSight to aggregate the total. I almost always see this issue when creating percent values specifically. The numerator and denominator are both partitioned at the tier level, so I genuinely think the order of operations breaks and totals are lost.
While I think it is unlikely that I would be able to retrieve a different result, I’d be willing to try if you are able to put an anonymized version of this into QuickSight Arena. That would allow me to try some alternative calculations using the same dataset fields. I can include some documentation below:
Thank you and let me know if you have any further questions!
