Sum total in pivot

Fri, 13 Feb 2015 09:40:30 GMT

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

These fields I use as dimension in the following tables:

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

Re: Sum total in pivot

rubenmarin — Fri, 13 Feb 2015 09:58:27 GMT

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"

Re: Sum total in pivot

Fri, 13 Feb 2015 13:24:50 GMT

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

topic Re: Sum total in pivot in QlikView

Sum total in pivot

Re: Sum total in pivot

Re: Sum total in pivot