Reverse Accumulation with multiple levels of granularity

[Mahan_018]

Hi Experts!

I am new to Qlik Sense. I made myself learn most of the topics that are required to use the tool properly. I was assigned a task to give the sum of the contribution amount for each month based on the current month. If the current month changes, the contribution amount also changes according to a "factor" that I came up with. I came up with a solution using rangeSum function. But the granularity kept on increasing. I'll attach the explanation of the granularity below. I'll also give the calculation I used for calculating the "factor" and the accumulation.

Factor calculation: 1+(year([Delivery Month])*12 + month([Delivery Month])) - (year(today())*12 + month(today()));

The [Delivery Month] field consists of months as Jan, Feb, etc., This factor changes according to to current month. If current month is January, then the factor will be different for all the months and if it is February, it will change accordingly.

Accumulation: Sum(Aggr( Rangesum(below(Sum({<Month={">=$(vMonth)"}>}Contribution),0,NoOfRows(Total))),[Country]));

vMonth has month(Today()); Country is one level deeper in granularity. I want a function that can dig deeper into the granularity like Month Contribution ---> Region Split Contribution ----> Country Split Contribution----> Department Split Contribution----> Product Split Contribution. If you add filters for each granularity, the values must be equal at both row level and column level.  I have attached something for reference (Not good at flow charts). Hope it helps!

 Regards,

Mahan.

[Oleg_Troyansky · Accepted solution]

Hi Mahan,

I don't know if I can build this fairly complex calculation for you within the scope of a forum message, however I can give you some pointers:

- Using Rangesum(Above() or Below() ) is a valid approach for this kind of a calculation, however it likely requires adding the Year/Month field into the list of dimensions for your AGGR() - otherwise Above/Below will be looking at a previous Country rather than the previous Month, unless that was your intention, and then I'm not sure I understand the logic of using Below

- The AGGR can only be as detailed as the list of its dimensions. So, the AGGR() by country will aggregate the data at the country level. If you wanted deeper levels of granularity, such as departments and products, then you'd need to add them to the list of the AGGR() dimensions and then manage the calculations between them.

- Overall, I have a feeling that you overly complicated this calculation. I believe it can be done a bit simpler, with a simple aggr() at the required level of detail. If it's built correctly, the further aggregation can be done by the chart (for example, a Pivot table) that would sum up the corresponding values that are generated by AGGR().

In order to better understand how the AGGR() function works, allow me to invite you to my online session on Set Analysis and AGGR at the virtual Masters Summit for Qlik. You will learn all the required methodologies for these types of analytical challenges. 

Cheers,

[Mahan_018]

Hi Oleg,

Thank you for the reply, suggestions and corrections. I got the idea of including some fields in the calculation after the reply and I am one step closer to the desired solution. Also, I will try my best to attend the summit. Should I register or is the entry free for community members? Let me know. I want to learn Qlik Sense at a much deeper level and if the summit will help me with that, I will gladly attend. 

Thanks and Regards,

Mahan

[Oleg_Troyansky]

Hi Mahan,

The Masters Summit for Qlik is an independent event, not associated directly with the Community. You can register for the 5 individual online sessions on our website:

https://masterssummit.com

To the best of my knowledge, this is the most advanced educational event for Qlik Sense. The depth of our content is unmatched. See you there!

Cheers,