Sum a static number list based on date level

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.

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!

Why are you taking the min?

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

1 Like

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.

What about when you use sum?

Summing it results in much higher values reported.

Have you tried using some level aware calculations?

I think that is what you’re looking for

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.

Hi @quintarium

What are your current calculations and are they throwing errors or do they have the wrong result?

Hi @quintarium

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

Hi Max,
My apologies for the late reply. This can be archived. I haven’t found a solution and my client decided to go with an alternative work around (modifying the underlying data instead of doing it in QS).

1 Like