We are trying to calculate cumulative sum of variable (Volume by Vertical and Product) based on Vertical and Year-Quarter. But when we are Aggregating 'Volume by Vertical and Product' it on Vertical and Quarter Level, it's giving us incorrect values, instead of required vertical/quarter it is giving count of entire vertical. We need to have benchmark on certain quarters that we have defined in our calculations
Please find attached Excel containing 2 Tabs - 'Raw Data' and 'Output'.
In 'Output' Tab, added 'Current Output by Qlik' and 'Expected Output'.
We have used below calculations:
LET time_variable = '[Year-Period]={"19/20-Q2","19/20-Q3"}';
LET volume_by_vertical = 'Count({1} {< $'&'(time_variable )'&',[Year]=,[Quarter]=,[Week]=,[Period]=>} TOTAL <[Vertical]> distinct [ORDER])';
LET volume_by_vertical_and_PRODUCT = 'rangesum(Count({1} {< $'&'(time_variable)'&',[Year]=,[Quarter]=,[Week]=,[Period]=>}
TOTAL <[Vertical],[Product Name]> distinct [ORDER]),Vertical&[Year-Quarter]&[Product Name])';
Calculation used to aggregate data on Vertical and Year-Quarter Level:
Aggr(RangeSum(Above(
sum( (Aggr(Count({1}{< $'&'(time_variable)'&',[Year]=,[Quarter]=,[Week]=,[Period]=>} TOTAL <[Mapped Product Name],[Vertical]>
distinct [ORDER_ID]),[Product Name],[Vertical])))
, 0, RowNo()
)),
([Year-Period] , [Product Name] )
)
