Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gal_polak
Creator
Creator

Pivot table totals functions - how is it calculated?

Hi all, 

We use the pivot table in multiple sheets with different dimensions and measures, and we always encounter the same issue.

the total values are not making any sense, 

i understand that when we use very complex set analysis in the measures it can be the cause, 

but basically what i would like to have is like in a simple table, the ability to select the total function, 

is there a way? 

 

for example we have the following pivot table (top table) 

clipboard_image_0.png

and what we see is that for the Org. unit total it is not a simple average 

when we convert to simple table (bottom table) and select "total function" AVG - only then the correct value is displayed. 

my measure is this: 

avg(EventValue)/3 

 

can some one explain how is the pivot table totals are being calculated? or how can i force a function like average?

Labels (3)
1 Solution

Accepted Solutions
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

First, create a new measure called Dimensionality().

You will notice there will be a different value assigned to total and non-total rows:

clipboard_image_0.png

You can control the expression for the Total rows by using conditional if..else

Eg: if(Dimensionality()=1, avg(Number),sum(Number))

 

Refer attached qvf for reference.

Thanks and regards,

Arthur Fong

Refer 

 

View solution in original post

1 Reply
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

First, create a new measure called Dimensionality().

You will notice there will be a different value assigned to total and non-total rows:

clipboard_image_0.png

You can control the expression for the Total rows by using conditional if..else

Eg: if(Dimensionality()=1, avg(Number),sum(Number))

 

Refer attached qvf for reference.

Thanks and regards,

Arthur Fong

Refer