Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

Totals on AVG expression

Hello:

Please have a look to the cyan column. I've this expression on a Pivot Table for the column Stock:

if (dimensionality()>=2,  avg(Stock * $(vPeso)),  sum(Stock * $(vPeso))   )

stock.JPG

I need change this part of the expression sum(Stock * $(vPeso)) for something else to get this total

Total 13767 = 6624 + 3140 + 361 + 3642

Instead of 247847 the sum of all items which average are 6624, 3140, 361, 3642.

Could you tell me what is the correct expresion to get this total ?

The AVG values for dimensionality 2 & 3 are corrects.

Thank you,

Joaquín

1 Solution

Accepted Solutions
Gysbert_Wassenaar

In your properties screenshot you have three dimensions. In your first screenshot you have two dimensions. If you keep changing the number of dimensions in between posts it makes it very hard to come up with correct expressions. You probably need to add the third dimension to the aggr function since you added it to the chart. Attached is an example with two dimensions. Play around with it to see how it works.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

You probably need to use an aggr in there to sum the aggregated averages:

sum(aggr(avg(Stock * $(vPeso)), ..Dim1,Dim2.. ))

I don't know your dimensions so replace ..Dim1,Dim2.. with the correct dimensions.


talk is cheap, supply exceeds demand
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II
Author

These are the dimensions of the Pivot Table; I've replacesd Dim1 by NombreTotal and Dim2 by Nombre Centro and doesn't work. Is this correct?

properties.JPG

Gysbert_Wassenaar

In your properties screenshot you have three dimensions. In your first screenshot you have two dimensions. If you keep changing the number of dimensions in between posts it makes it very hard to come up with correct expressions. You probably need to add the third dimension to the aggr function since you added it to the chart. Attached is an example with two dimensions. Play around with it to see how it works.


talk is cheap, supply exceeds demand
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II
Author

Thank you very much Gysbert.

It was an involuntary error hidding columns, but your answer show me the correct way.