Percent of total on a calculated dimension

[leenlart]

Hello, 

I've got an app based on the value of the stock in the warehouses.  

I need to create a pivot table with as lines : the product type, as columns : the calculated dimension 'Stock Rotation', and as a mesure the percent of total value.  So we're showing the percent of total value of Stock Rotation by product type.  

My calculated dimension 'Stock Rotation' looks like this : 

=aggr(if(match(sum(STOCK)-[Mvts Conso 12 mois],[Mvts Achats 3 mois]),'G. New réf',
if([Delai rotation mois (bis)]>= 24, 'F. Dormants',
if([Delai rotation mois (bis)]>=18, 'E. < 24 mois',
if([Delai rotation mois (bis)]>=12, 'D. < 18 mois',
if([Delai rotation mois (bis)]>=6, 'C. < 12 mois',
if([Delai rotation mois (bis)]>=3, 'B. < 6 mois',
if([Delai rotation mois (bis)]>=0, 'A. < 3 mois', 'F. Dormants')
)))))), ARTICLE_ID)

If the (stock - consommation typed movements) equals the purchasing in the last 3 months, the product is classed as a new reference.  Otherwise, we have a measure that calculates the Rotation for the stock in months, and this  classifies the product in the categories.  

My Value mesure looks like this :  

sum( {$<ANNEE={$(=Only(ANNEE)-1)}, MOIS={'déc.'}, SEMAINE=, PRECHRO_SIT={'2STK'} >} VALUE) +
(sum( {$<SENS_MVT={'E'}, DATE_JOUR={"<=$(=Weekend(MakeWeekDate(ANNEE,SEMAINE)))"}, MOIS=, SEMAINE= >} VALUE_MVT)
- sum( {$<SENS_MVT={'S'}, DATE_JOUR={"<=$(=Weekend(MakeWeekDate(ANNEE,SEMAINE)))"} , MOIS=, SEMAINE= >} VALUE_MVT))

We add the stock situation value at the end of the last year, and then add in all the stock movements (entry or exit movements)

I've tried creating the percent of total value by just doing the following, but it gives incorrect percents.  

(sum( {$<ANNEE={$(=Only(ANNEE)-1)}, MOIS={'déc.'}, SEMAINE=, PRECHRO_SIT={'2STK'} >} MTPXREVSTAT_STD_S_SIT) +
(sum( {$<SENS_MVT={'E'}, DATE_JOUR={"<=$(=Weekend(MakeWeekDate(ANNEE,SEMAINE)))"}, MOIS=, SEMAINE= >} MTPXREVSTAT_STD_S_MVT)
- sum( {$<SENS_MVT={'S'}, DATE_JOUR={"<=$(=Weekend(MakeWeekDate(ANNEE,SEMAINE)))"} , MOIS=, SEMAINE= >} MTPXREVSTAT_STD_S_MVT)))
/
(sum(total {$<ANNEE={$(=Only(ANNEE)-1)}, MOIS={'déc.'}, SEMAINE=, PRECHRO_SIT={'2STK'} >} MTPXREVSTAT_STD_S_SIT) +
(sum(total {$<SENS_MVT={'E'}, DATE_JOUR={"<=$(=Weekend(MakeWeekDate(ANNEE,SEMAINE)))"}, MOIS=, SEMAINE= >} MTPXREVSTAT_STD_S_MVT)
- sum(total {$<SENS_MVT={'S'}, DATE_JOUR={"<=$(=Weekend(MakeWeekDate(ANNEE,SEMAINE)))"} , MOIS=, SEMAINE= >} MTPXREVSTAT_STD_S_MVT)))

How can I create this table ?  I'm using Qlik Sense Enterprise Nov 2025. 

Thanks for your help!! 

[Daniel_Castella · Accepted solution]

Hi @leenlart 

Without seeing the data is a bit difficult to follow the logic but, at first sight, I think the problem is that you are doing the Totals without considering the Stock Rotation calculated dimension or the Product Type.

I mean, when you set the Total in the denominator, it will add all the values. For example, if you have

	Stock Rotation 1	Stock Rotation 2
Product Type 1	10	20
Product Type 2	30	40
Product Type 3	50	60

The Total will be 10+20+30+40+50+60 = 210 for all the cells. If you want to calculate the Total over a field, it needs to be set like this Total<Product Type> or Total<Stock Rotation>. By doing this, the denominator in the example will be:

Total<Product Type> = 30 for Product Type 1 cells, 70 for Product Type 2 cells and 110 for Product Type 3 cells.

or 

Total<Stock Rotation> = 90 for Stock Rotation 1 cells and 120 for Stock Rotation 2 cells.

Note that implementing this for the calculated dimension can be a bit difficult. Hence, I would recommend to create that dimension in the script if possible.

Let me know if it works for you. If not, could you, please, provide a data sample in order I can reproduce your logic in my end?

Kind Regards

Daniel

[leenlart]

Hello, 

I agree, I think my problem is coming from not taking into account the Stock rotation dimension.  

Thank you for your table, and examples, that makes things a lot easier to wrap my head around.  

I think I might have to do something in the script to get around this, I'm just not sure what yet!  

In any case, thank you for taking the time!