Above with cycle groups (year-over-year KPI)

[roland_berger]

Hi Qlik Community!

There are already a couple of threads on this topic, I was however not able to answer my question with them. I need to do a year-over-year comparison (compare Jan17 with Jan16, Feb17 with Feb16 etc) and calculate the growth rate. I would like to do this in a pivot table. So far, so easy, since I would just do sum(xy)/above(sum(xy)). The table however includes besides year and month also a third dimension, which is a cycle group (in the example below called 'Color'). And that's where I get lost.

The output of my above function (or my denominator) should be like Table 2 in the screenshot that I attached, i.e. I need to find the expression that would give me Table 2. Table 1 is generated just by using sum(xy) as expression. I already messed around with total and aggr() but wasn't able to get the correct result.

Can anybody help me with this?

Thanks in advance!

Best regards,

Roland B.

[alex00321]

Hi would you explain what exactly you want and maybe share the necessary files? Thanks!

[Brett_Bleess]

Roland, have a look at the following Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/Year-over-Year-Comparisons/ba-p/1462927

If that is not quite it, use the following link to continue searching for other posts in the Design Blog area, as I suspect you may find something there to help further.  Once you do find something, it would be great if you can post what you ended up doing to close out your thread here, just post what you did, and once posted, use the Accept as Solution button on that post to mark it.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett