Qlik Sense Pivot Table: Calculate percentage of previous row

[SBN]

Dear fellow Qlik enthousiasts,

I'm trying to calculate a percentage of the previous dimension in a pivot table in Qlik Sense.

In the example above I'm showing sales per year, per customer, per product.
Now the percentage of sales should be the percentage based on the previous dimension.

So if you want to switch "Customer" and "Product" the percentage should recalculate the percentage based on the new order in the pivot table.

I know you can use the following formula to calculate the percentage based on a certain reference to a field name:
Sum(Amount)/Sum(TOTAL <CategoryFieldName> Amount)

But then when you want to switch dimensions in the pivot table your calculation is "fixed" to a certain field name or set of field names.
And when you want to add new fields to the pivot table to create more analysis then this calculation would adapt regardless of the "levels" of dimensions.

Is this possible?
And if so, how would I do this...😉

Kind regards,

SBN

[brunobertels]

hi 

you can achieve this by using dimentionality in your mesure 

see example here 

https://community.qlik.com/t5/New-to-Qlik-Sense/Row-percent-for-subtotals-in-pivot-table-Qlik-Sense/... 

[SBN]

Hi Bruno,

Thank you very much for your time to respond to my question!

The thread gave me some more insights in this matter.

Attached is an example QVD with the data as shown in my initial question.
I've managed to get it to work in this simplified example, but I'm running into some issues.

This is the initial "order" of the pivot table: "Year", "Customer", "Product", "Order".

If I expand the "Year" and "Customer1" or "Customer2" the right "Percentage" is shown for "Customer1" and "Customer2" in relation to the "Year".
The "Percentage" of the "Products" are shown in relation to the "Customer".
So far so good...😉

Now when I change the order of the pivot table, which is a very nice feature of the pivot table, by moving the "Product" one "Level" up the tree, I run into issue that the percentages are not calculated correctly.

This is just a simplified example of what I'm trying to accomplish.
I haven't tried to fix this, but I think that I need to add some sort of check for the combination of dimensionality and dimension, but you can imagine that with like 15 dimensions this will get very complex.

Any way to do this without setting the fixed field names in the expression?

Kind regards,

SBN