How to use Aggr() with Dimension of Previous Year

[MarcoLuk]

I encountered a problem that I am using aggr() with dimensions but I need to do a YoY comparison in KPI field. Therefore the previous year data are needed to aggr() with previous year's dimension. Yet, the calculation only involves dimension of current year. How can I make use of preivous year's dimensions. Thank you.

eg: 
dimensionCurrent: benz, toyota, honda
dimensionPrevious: benz, toyota, honda, tesla

formula: sum(aggr((xxx), dimension) - aggr((xxx), dimension)).
My expectation: sum(aggr((xxx), dimensionCurrent) - aggr((xxx), dimensionPrevious))
Actual: sum(aggr((xxx), dimensionCurrent) - aggr((xxx), dimensionCurrent)).

[marcus_sommer]

The use of aggr() is usually not needed to compare data against periods. Beside of this it's not really clear how your view should look like - therefore more details and screenshots of the objects will be helpful.

[Kushal_Chawda]

@MarcoLuk  Can you share example of what you are trying to achieve?

[MarcoLuk]

@marcus_sommer @Kushal_Chawda Thank you for replying.

As example I have a table as below,

Route              Train#     #Travellers   weighting                                       operating cost

LA -> SEA       1              10000          10000/sum(Total of #travellers)     5000
LA -> MIA        2              20000          20000/sum(Total of #travellers)     2000
MIA -> NYC    3               40000         40000/sum(Total of #travellers)     3000

I want to compare the "operating cost * weighting" for a year and i want to compare it with the number of previous year. The script (expression) for current year will be something like
sum(aggr(sum(num(travellers))/sum(total(num([travellers]))) * num([operating cost]), [Route]).

and YoY comparison will be 

sum(aggr(sum(num([travellers]))/sum(total(num([travellers]))) * num([operating cost]), [Route]) - sum(aggr(sum(num({year set}[travellers]))/sum(total(num({year set}[travellers]))) * num({year set}[operating cost]), [Route])

However, if I am using KPI, the Route dimension for the aggr() will be the Route for current year. Since, Route for this year and previous year are different, the ultimate calculation will be wrong since the data for previous year is using wrong dimension Route1 instead of Route2 for previous years' aggr()

[marcus_sommer]

It looked a bit more complicated as necessary. Here a simplified (one Field without any weighting and only a year set analysis) example of how I would try to solve the case:

sum({ CY } Field) - sum({ LY } Field)

the dimensions within the table will then define the dimensional context of the calculation.

By applying such logic within a global KPI without further dimensions it might be look like:

sum(aggr(sum({ CY } Field) - sum({ LY } Field), Route))

which aggregates all differences per Route. If the weighting needs to be calculated on a different context the relevant dimensions might be included or a more complex logic would be needed. But before starting of nesting aggr() it should be considered to adjust the data-model.

[MarcoLuk]

@marcus_sommer Thank you for your reply. I realized the problem is caused by the dimension (also a search box) of "Route". If I pick "Select possible", the "Route" set for each year will be correctly matched with corresponding year and the result is correct. However, by default, the calculation for LY and CY are both calculated with dimension of CY's "Route" which is wrong.

I am figuring the way to apply "Select possible" in my calculation.

Link: https://community.qlik.com/t5/Design/All-About-Selections/ba-p/1475260

[marcus_sommer]

This kind of topic could be also addressed within the set analysis by using the p() and e() functions which could contain also an own set analysis and further be combined with operators and/or nested in multiple ways.

Nearly each sub-set of data are selectable in this way - there are no real technically limitations else only in regard to the logically comprehending of the data-model / data-set to the wanted/needed dimensionality within the objects and selections.

Especially if already "simple" sum/count-aggregations are needing nested/combined selection-layers an adjustment of the data-model in the direction of simplification should be considered because otherwise the calculation of rates/aggr-constructs/accumulations/... on top of it becomes easily a nightmare ...

Therefore I suggest to check if the current association of year and route within the data-model could be adjusted in a way that a year-selection has no dependency to the route unless to the real exists values.

[MarcoLuk]

@marcus_sommer I really appreciate your help in my 2 recent questions. I found a walkaround to solve the problem but with more complexity. Once again, thank you very much for your help in the past 2 weeks!!!