TOTAL VALUE like Absolute Value in PIVOT row

[giuval45]

Hi,

I made several attempts, but was not able to solve this problem ...

I am not able to calculate and display the Total repeated for each column in the Total Lines...

… and so I cannot calculate the “% of Launched and Received Qty” on “ Qty Ordered” ... in Total Lines (it works on Product rows)

With the formula:

sum(DISTINCT total <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> [Ordered Qty])

I can put the total value of the "Ordered Qty" repeated at the top of each column, for each date, for any product ... and so I can calculate, always by Product rows:

Qty Launched : if(sum([Launched Qty])=0,alt(BEFORE([Qty Launched]),0),Sum([Launched Qty]))

Launched Qty ACC : RangeSum(Alt(BEFORE([Launched Qty ACC]), 0), Sum([Launched Qty]))

% Launched : =RangeSum(Alt(BEFORE([Launched Qty ACC]), 0), Sum([Launched Qty]))/sum(total <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> [Launched Qty])

% Launched on Ordered : =alt([Launched Qty ACC]/[Qty Ordered],0)

Qty Received : if(sum([Received Qty])=0,alt(BEFORE([Qty Received]),0),Sum([Received Qty]))

Received Qty ACC : =RangeSum(Alt(BEFORE([Received Qty ACC]), 0), Sum([Received Qty]))

% Received : =RangeSum(Alt(BEFORE([Received Qty ACC]), 0), Sum([Received Qty]))/sum(total <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> [Received Qty])

% Received on Ordered : =alt([Received Qty ACC]/[Qty Ordered],0)

Instead, if I use the same formula in the Dimension of Total Lines, I receive a Wrong Total…

The only way, I found, to calculate a correct Total, at Total line level, is:

IF(DIMENSIONALITY()<=10,

sum(AGGR(MAX ([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size])),

…

But this formula do NOT repeat the obtained value in each column and this don’t permit to calculate the progressive value of:

% Launched on Ordered

e

% Received on Ordered

Pratically, I need to find a way to repeat the a.m. correct Total, in the Total Lines of all the columns.. a sort of “Absolute Value”, like in Excel:

I added, also, a Calculate Dimension, to visualize, for each Product, the “Total Ordered Qty”, with the formula:

=num(AGGR(sum([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size])/AGGR(COUNT([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size]),'#,##0')

But this value appear only on the Product rows and NOT in Total Lines…

Is there any way to visualize the Correct Total also at Total Lines level?

Please find attached the Data Input file in: Tabella_Dati.xlsx, the sample Dashboard: ProdProgression.qvw with the PIVOT with this problem in the Sheet: Production Progress - Pivot ORIZZONTALE su Data - LAST - DETAIL  and a sample Output in Excel with the Wrong values from this PIVOT in: “da Qlik” and “how should be” in: “CORRETTO” in the Excel file: Esempio di Output PIVOT con Totali non ripetuti nelle righe dei Totali.xlsx 

Many thanks for your attention and help…

Best regards,

Giulio

[Anonymous · Accepted solution]

Hi Giulio,

try to replace the formula of Qty Ordered with this:

//OLD Expr.

IF(DIMENSIONALITY()<=10,

  sum(AGGR(MAX ([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size])),

  sum(DISTINCT total <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> [Ordered Qty])

)

//NEW Expr.

IF(DIMENSIONALITY()<=10,

  sum(TOTAL <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> AGGR(MAX ([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size])),

  sum(DISTINCT total <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> [Ordered Qty])

)

you can also use this one, without the If and the Dimensionality(), it should work the same...

    sum(TOTAL <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> AGGR(MAX ([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size]))

Cheers

Pier,

[giuval45]

Received solution from Pier Francesco

Many thanks for your precious help... 😉

• sum(TOTALTipo,[Class],[Model],[Part],[Color],[Label],[Size]> AGGR(MAX[Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size]))

[Anonymous]

De nada

[giuval45]

Hi Pier,

your formula:

sum(TOTAL <Tipo,[Class],[Model],[Part],[Color],[Label],[Size]> AGGR(MAX ([Ordered Qty]),Tipo,[Class],[Model],[Part],[Color],[Label],[Size]))

is Perfect

and it responds perfectly to the Latin motto :

Simplex sigillum veri !

Really many thanks,

Giulio