Sum a static number list based on date level

Question & Answer
,
1

I have a static list of values that set up in Quicksight calc.

ifelse(
Week=1 AND Store='TUS',387,
Week=1 AND Store='BCA',92,
Week=1 AND Store='EUB',352,
Week=1 AND Store='FANN',98,
Week=1 AND Store='BCA and FANN',190,
Week=1 AND Store='BCA, FANN and CABL',205,
Week=1 AND Store='PJ',165,
Week=1 AND Store='TFN',171,
Week=1 AND Store='AUV',21,
Week=1 AND Store='TRT',27,
Week=1 AND Store='PQNQ',0,
Week=1 AND Store='CABL',15,
Week=1 AND Store='AEAE',6,
Week=2 AND Store='TUS',433,
Week=2 AND Store='BCA',174,
Week=2 AND Store='EUB',394,
Week=2 AND Store='FANN',15,
Week=2 AND Store='BCA and FANN',189,
Week=2 AND Store='BCA, FANN and CABL',212,
Week=2 AND Store='PJ',165,
Week=2 AND Store='TFN',188,
Week=2 AND Store='AUV',34,
Week=2 AND Store='TRT',83,
Week=2 AND Store='PQNQ',0,
Week=2 AND Store='CABL',23,
Week=2 AND Store='AEAE',3,
Week=3 AND Store='TUS',318,
Week=3 AND Store='BCA',115,
Week=3 AND Store='EUB',466,
Week=3 AND Store='FANN',12,
Week=3 AND Store='BCA and FANN',127,
Week=3 AND Store='BCA, FANN and CABL',130,
Week=3 AND Store='PJ',206,
Week=3 AND Store='TFN',187,
Week=3 AND Store='AUV',17,
Week=3 AND Store='TRT',74,
Week=3 AND Store='PQNQ',0,
Week=3 AND Store='CABL',3,
Week=3 AND Store='AEAE',6,
Week=4 AND Store='TUS',193,
Week=4 AND Store='BCA',38,
Week=4 AND Store='EUB',363,
Week=4 AND Store='FANN',29,
Week=4 AND Store='BCA and FANN',67,
Week=4 AND Store='BCA, FANN and CABL',68,
Week=4 AND Store='PJ',122,
Week=4 AND Store='TFN',190,
Week=4 AND Store='AUV',25,
Week=4 AND Store='TRT',35,
Week=4 AND Store='PQNQ',0,
Week=4 AND Store='CABL',1,
Week=4 AND Store='AEAE',13,
Week=5 AND Store='TUS',342,
Week=5 AND Store='BCA',84,
Week=5 AND Store='EUB',551,
Week=5 AND Store='FANN',76,
Week=5 AND Store='BCA and FANN',160,
Week=5 AND Store='BCA, FANN and CABL',160,
Week=5 AND Store='PJ',106,
Week=5 AND Store='TFN',207,
Week=5 AND Store='AUV',24,
Week=5 AND Store='TRT',53,
Week=5 AND Store='PQNQ',10,
Week=5 AND Store='CABL',0,
Week=5 AND Store='AEAE',1,
Week=6 AND Store='TUS',387,
Week=6 AND Store='BCA',67,
Week=6 AND Store='EUB',554,
Week=6 AND Store='FANN',79,
Week=6 AND Store='BCA and FANN',146,
Week=6 AND Store='BCA, FANN and CABL',146,
Week=6 AND Store='PJ',198,
Week=6 AND Store='TFN',175,
Week=6 AND Store='AUV',26,
Week=6 AND Store='TRT',63,
Week=6 AND Store='PQNQ',0,
Week=6 AND Store='CABL',0,
Week=6 AND Store='AEAE',3,
Week=7 AND Store='TUS',431,
Week=7 AND Store='BCA',29,
Week=7 AND Store='EUB',552,
Week=7 AND Store='FANN',31,
Week=7 AND Store='BCA and FANN',60,
Week=7 AND Store='BCA, FANN and CABL',60,
Week=7 AND Store='PJ',320,
Week=7 AND Store='TFN',164,
Week=7 AND Store='AUV',34,
Week=7 AND Store='TRT',56,
Week=7 AND Store='PQNQ',0,
Week=7 AND Store='CABL',0,
Week=7 AND Store='AEAE',5,
Week=8 AND Store='TUS',391,
Week=8 AND Store='BCA',44,
Week=8 AND Store='EUB',641,
Week=8 AND Store='FANN',142,
Week=8 AND Store='BCA and FANN',186,
Week=8 AND Store='BCA, FANN and CABL',208,
Week=8 AND Store='PJ',174,
Week=8 AND Store='TFN',178,
Week=8 AND Store='AUV',44,
Week=8 AND Store='TRT',38,
Week=8 AND Store='PQNQ',0,
Week=8 AND Store='CABL',22,
Week=8 AND Store='AEAE',0,
Week=9 AND Store='TUS',483,
Week=9 AND Store='BCA',97,
Week=9 AND Store='EUB',799,
Week=9 AND Store='FANN',91,
Week=9 AND Store='BCA and FANN',188,
Week=9 AND Store='BCA, FANN and CABL',215,
Week=9 AND Store='PJ',158,
Week=9 AND Store='TFN',398,
Week=9 AND Store='AUV',26,
Week=9 AND Store='TRT',54,
Week=9 AND Store='PQNQ',0,
Week=9 AND Store='CABL',27,
Week=9 AND Store='AEAE',0,
Week=10 AND Store='TUS',418,
Week=10 AND Store='BCA',55,
Week=10 AND Store='EUB',805,
Week=10 AND Store='FANN',119,
Week=10 AND Store='BCA and FANN',174,
Week=10 AND Store='BCA, FANN and CABL',189,
Week=10 AND Store='PJ',54,
Week=10 AND Store='TFN',339,
Week=10 AND Store='AUV',30,
Week=10 AND Store='TRT',43,
Week=10 AND Store='PQNQ',0,
Week=10 AND Store='CABL',15,
0
)

I then show these values in a pivot table. For example, the values for the TUS store, week 1, are correct, with 387. For Week 2, it’s 433. Week 3 it’s 318, etc.

In addition, I have a date drill down consisting of a quarter calc, month calc and a week dimension, which I place in the Columns field wells.

quarter_calc: = truncDate(‘Q’,{Date Value})
month_calc: = truncDate(‘MM’,{Date Value})
Week = dimension metric from data set.

Getting to the actual question now - When I drill up in the pivot table to view numbers by Month or Quarter, the numbers that are displayed are incorrect. I am thinking it’s because of my ifelse statement hardcoding values, but is there a way for me to Sum based on the date level?
so if the level in the pivot table date_Month_calc = 1 then sum the first 4 weeks, if the date level is date_Quarter_calc=1 then sum up the first 3 months, etc.

Is this possible to do in this use-case?

Thank you for any assistance provided.

2

2023_05_14_20_23_43_Instructions_050407.xlsx_analysis_Brave
2023_05_14_20_23_43_Instructions_050407.xlsx_analysis_Brave1575×765 26.5 KB

This is the correct “view” when looking at the numbers by week. However, when I collapse the month or quarter columns, that’s when I experience the issue reported.

Thank you in advance!

3

Why are you taking the min?

That will give you the min of all of the values.

4

Hey Max,
The calculation (my ifelse calc at the top of this page) is not aggregated, so I need to choose some aggregation when adding it to the visual. I can choose Average, Median, Min, Max, to display the correct values.

5

What about when you use sum?

6

Summing it results in much higher values reported.

7

Have you tried using some level aware calculations?

I think that is what you’re looking for

8

I did try, but I can’t figure out the right syntax or right laa for this use-case. I don’t know if what I’m trying to achieve is something that laa can’t do, or if my syntax is incorrect.

If I take the values from the ifelse formula, and I want them to apply based on the date level (week/month/quarter), is that PRE_AGG, POST_AGG, etc? I wasn’t able to identify that on my end.