Aggregation on Table calculation not working

Question & Answer
, , ,
1

Hi QuickSight Community!

I have a question about the level-aware calculation/table calculation that I’ve been stuck for a while and need your help.

here’s the sample of data:
Flow--------------------------------Sales Amount
US > New Customer > Desktop—$6,500
Jan 28, 2024-----------------------$1,800
Feb 4, 2024------------------------$1,200
Feb 11, 2024-----------------------$1,500
Feb 18, 2024-----------------------$2,000
Note: Sales Amt is calculated at the “Flow” and “Week” level, there’re multiple flows in the data

For all the 4 weeks Sales data, I want to calculate
(1) Latest week’s Sales Amt
(2) Previous 3 week’s Sales Amt
(3) Difference between 1) & 2)

I am able to get 1) & 2), but when doing 3) which is 1) - 2), the number doesn’t show up on the table.

Here are the formula I’m using:

(1) sum(ifelse({week}={latest_week},{sales_amt},NULL),[{Flow},{week}])
(2) sum(ifelse({week}<>{latest_week},{sales_amt},NULL),[{Flow},{week}])/3
(3) sum(ifelse({week}={latest_week},{sales_amt},NULL),[{Flow},{week}]) - sum(ifelse({week}<>{latest_week},{sales_amt},NULL),[{Flow},{week}])/3

(1) & (2) is showing the correct results but 3) is blank on the dashboard like below:

|Flow------------------------------|1) Latest Week Sales|2) Avg of Previous Weeks’ Sales|Diff of 1) and 2)|
|US > New Customer > Desktop|$2,000----------------|$1,500-----------------------------|(Blank)----------|

Can someone help me with the 3) calculation? I’ve tried pre_agg/post_agg_filter but can’t tweak the formula to get the correct result

Thanks in advance!

2

Hi @mtu Welcome to the QuickSight Community! Thanks for posting your question. I am pinging one of our SAs to make sure your question gets a reply on Monday if we don’t hear back from the community before then. Thx!

1 Like
3

Hello @mtu, I think you are on the right track here. Maybe you want to switch you calculations to a sumOver rather than a sum function to utilize the LAC-W aggregations. It may also be worth wrapping the 2nd portion of the function in parenthesis to ensure it runs the functions in the correct order.

One other thing to consider is, you are partitioning your denominator value by weeks and then also dividing it by 3. Instead you want to either sumOver the sum of each week then divide it by 3, or wrap it inside an avgOver for the Flow. I’ll write out the 2 options below:

option1 = sumOver(ifelse({week}={latest_week},{sales_amt},NULL),[{Flow},{week}], PRE_AGG) - (sumOver(sumOver(ifelse({week}<>{latest_week},{sales_amt},NULL),[{Flow},{week}], PRE_AGG), [{Flow}], PRE_AGG)/3)

option2 = sumOver(ifelse({week}={latest_week},{sales_amt},NULL),[{Flow},{week}], PRE_AGG) - (avgOver(sumOver(ifelse({week}<>{latest_week},{sales_amt},NULL),[{Flow},{week}], PRE_AGG), [{Flow}], PRE_AGG))

The first option leaves you in control of how the average is calculated and the 2nd function uses avgOver to handle the aggregation. One of these should lead you to your desired output, but let me know if you have any further questions!