Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 (2)
0 Replies