Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
mjtaft2017
Partner - Creator
Partner - Creator

Qlik Sense Pivot Table Totals not aggregating correctly

 In Qlik Sense I have a pivot chart as shown below.  A product can have multiple sales codes and I need to display the count of products built and sold by month using sales code as a dimension.  My original expression for the measures:

count({<DateType={'Built'}>}Distinct [ProductID]).  ( I change DateType to 'Sold' for the other measure)

I did not use aggr at all.  Then realized the totals were off.  After reading as many posts as I could on AGGR and pivot tables I tried this expression for my measure - and it still gave incorrect totals.

sum(aggr(count({<DateType={'Sold'}>}Distinct [ProductID]),Year,Month,[Sales Code]))

I  was able to get the correct numbers by checking the dimensionality() to determine what dimensions to apply AGGR to and when

IF(Dimensionality()=0,sum(aggr(count({<DateType={'Sold'}>}Distinct [ProductID]),Year,Month)),
sum(aggr(count({<DateType={'Sold'}>}Distinct [ProductID]),Year,Month,[Sales Code])))

The problem is now it takes extremely long to render the chart.   I hope someone can help with this as I have several similar charts to make.

Below chart 1  with my original measure expression and also using aggr without the addition of the IF statement for dimensionality.

QlikSense_Pivot.PNG

 

 Below is chart 2  I was able to get correct totals using aggr and dimensionality however the chart takes too long to render.  I don't understand why I had to use dimensionality to get the correct totals  

Correct if I use Dimensionality() with aggrCorrect if I use Dimensionality() with aggr

 

 

 

Labels (3)
0 Replies