Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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] )
)
Raw data that you provided doesn't look like raw data... it seems that this is pre-aggregated because your expression uses Order ID whereas I see Volume by Vertical and Product which looks like an expression. Would you be able to share the actual raw data or may be a sample app where we can see this?