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: 
Not applicable

Sum total in pivot

Guys,

I need help to get a sum total (straight table like) in a Pivot table. I allready enabled the "Show partial sum" but this won't give the right number of totals.

I think the solution is not easy and I think it needs some advanced expression skills.

Situation:

I use an input field to categorise fields

inputfield.JPG

These fields I use as dimension in the following tables:

result.JPG

In the red square I fill in a number (inputsum). This number will be devided over the categories in the first tabel. The second table shows the same numbers, only rounded. The Third table shows the remainder of the original number minus the rounded number. This table is the problem.

It shows the total of the remains of the three categorised field values (test). The expression i use is:

SUM( AGGR(

//Original calculation

  (sum(Aggr(

  sum(Counter)/sum(TOTAL <UitwisselbareFunctie> Counter)

  *

  Sum(TOTAL <UitwisselbareFunctie> ReductieInputFunctie), Leeftijdscategorie_Afspiegelen, UitwisselbareFunctie))

  -

//Minus rounded number

  floor(sum(Aggr(

  sum(Counter)/sum(TOTAL <UitwisselbareFunctie> Counter)

  *

  Sum(TOTAL <UitwisselbareFunctie> ReductieInputFunctie), Leeftijdscategorie_Afspiegelen, UitwisselbareFunctie))))

, UitwisselbareFunctie, Leeftijdscategorie_Afspiegelen))

Hope someone can help me

Cheers

2 Replies
rubenmarin

Hi Jeffrey, can you upload a sample to make tests?

Anyway, can you try to change the order of the first aggr, original and floor has:

"Leeftijdscategorie_Afspiegelen, UitwisselbareFunctie"

but the global aggr has this order switched:

"UitwisselbareFunctie, Leeftijdscategorie_Afspiegelen"

Not applicable
Author

Hi Ruben, I changed it but It didn't change anything. I will try to create a sample file, but it is complicated so give me some time