Multi-dimensional Pivot Table Aggregation, Feasible or not?

[aaditya1903]

I have a pivot table on qlik sense with dimensions as follows: fiscal_year (e.g. 2023) which expands to post_month (1-12) which expands to material_group_short_desc which expands to material_desc. I'm trying to do a monthly spending vs cumulative average for my measures.

my monthly spending formula is simple and works even with any filters selected in the pivot table as well as with all possible expansions of the pivot table, so even if it is fully expanded, etc. here is the formula: Sum({$<general_ledger_account = {'xxxxxxxx'}>} company_ccy_amount)

I need help in coming up with the cumulative average column. I am really struggling to get it to work if it fully expands or if any filters are applied. Here is an example of fully expanded table: 2023 shows average as the sum of everything in the year divided by the number of months passed. when it breaks down into months, it should be YTD style like if I am checking 2023 march, the cumulative average should be spending in jan+feb+mar / 3, etc. For the material group level, the individual group spending should be accumulated and divided by in whichever month it is expanded. For example, for material group amenity kits in 2024 april, the cumulative average should be monthly spending on amenity kits in jan+feb+mar+apr of 2024 / 4. Similarly for the material level.

[Oleg_Troyansky]

Hi there,

Consider using these two features:

1. Dimensionality() - if you need to apply different calculations at different levels of aggregation, this function can tell you what level you are on. The table total has dimensionality 0, the Year total will have dimensionality 1, etc. Based on that, you can formulate your various formulas.

2. Partial TOTAL sums - you can "break out" of certain dimensions by using the TOTAL qualifiers. For example:

     sum( TOTAL <Year, Material> Amount)

produces the total of amount by Year and Material, disregarding all other dimensions, like Period. For your purposes, you will have to add YTD conditions to avoid summing up all months. Unfortunately, Set Analysis cannot be used in this case, because it cannot be sensitive to your chart dimensions, therefore you will have to use IF() conditions. 

With these two instruments in mind, you should be able to formulate your expression.

Cheers,

Oleg Troyansky

P.S. Join my Qlik Expert Class in Vienna on September 22-24 to learn advanced Qlik development techniques, including data modeling, scripting, Set Analysis and AGGR, and performance optimization. 

[aaditya1903]

Thank you for your reply, you've already helped me a lot by introducing me to the idea of Dimensionality() but I still haven't quite figured the task out completely. So if you can help a little more, I'd really appreciate that.

How do I add YTD conditions? That is the part I'm struggling with now. I am finding it quite difficult to do the cumulative sum because I'm not sure how to ask qlik sense to sum something up from 1/1 of the year selected to the last day of the month and year selected. If you could suggest something for this, I think I'd be able to fully solve the problem.

Thanks again for all your help!

[hanna_choi]

Hello @aaditya1903 

could you share us sample data and the result pictures you want?

Cheers!

[aaditya1903]

I have added the ideal look of the pivot table for year and month as an attached file. If further expanded, please refer to dummy data and results expected below:

 

I quickly made this sheet to explain the calculation for material group, the issue is there will be more than one material group. And then each material group will also be able to be expanded to various materials. The same logic should apply for those materials. It should be (YTD spending of that particular material up to the selected month / the selected month).

So for march 2023's cumulative average for a particular material:

(monthly spending of the material in jan2023 + feb2023 + mar2023) / 3

I know this is not perfect dummy data or results data, but the best way to display what I want would be in a pivot table, and if I had a perfect table, I would not be on this forum haha.

Thank you for all your help!

[hanna_choi · Accepted solution]

Hello @aaditya1903 

How about this ways?

I'm used rangesum(), Above() functions.

Expression : RangeAvg(Above(Sum([Monthly Spending]), 0, 12))

Cheers!!