partial sums pivot table not shown

[#1]

Hey guys,

I have a question concerning partial sums in a pivot table.

You can find a file underneath.

The idea of the exercise:

*EENHPR is the price per unit (ton) of an article (Artikelnr)

*Sales2013 is the total sales of that unit (in yr 2013, or 2012)

I now want to calculate the tons that have been sold per article, so basic math: Sales/EENHPR = tons

I need to know the total tons per Name (partial sum Artikelgroep3) and the total tons per Article (partial sum Artikelnr)

As you can see, it does not want to show me the total amount (partial sums)..

Who can help me out?

Cheers,

Zipke

[Gysbert_Wassenaar]

You need to use an aggregation function on EENHPR. Try max:

=(sum(Sales13))

/

max(EENHPR)

[#3]

I tried to use your solution and it now does show me something, but the values are not correct?

None of the partial sums is correct..

[alexandros17]

If you think as qlik, it has different values for EENHPR so it can't choose which one to use ...

The solution is to compute values in the script and then sum values of the new computed field.

Hope it helps

[#5]

What do you mean different values?

I am just asking it to divide two values and then make a sum of those values?

They are all numbers?

The calculation is correct, so it does know what to do, it just needs to sum up the different lines.

[Gysbert_Wassenaar]

Ah, right. Need to aggregate that. Try:

=sum(aggr((sum(Sales13))

/

max(EENHPR),[Top klanten GIPS],Artikelgroep3,Artikelnr,EENHPR))

[#7]

Awesome, it worked!

Thank you so much!

Could you just explain me what exactly you have changed now?

Why do I use the "max" and what does the aggregation do?

Cheers

[Gysbert_Wassenaar]

The pivot table can't calculate the subtotal for =(sum(Sales13))/EENHPR because at that level there are several values for EENHPR. That's why the aggr is necessary so we get calculate sums of the calculated values on the rows. The max isn't even necessary. It works without the max:

=sum(aggr((sum(Sales13))

/

EENHPR,[Top klanten GIPS],Artikelgroep3,Artikelnr,EENHPR))

See this blog post for more information about aggr

[#9]

Hey Gysbert,

Thank you for the explanation but unfortunately there is still a problem..

The EENHPR (this is the price per unit) is just a text to Qlikview, instead of a value. I found this out when I exported the table to Excell, it did not want to calculate it with that column, unless I transformed it to a number.

It still shows me wrong calculations, please have a look at the print screen.

The partial sums are correct, but the values (the calculations) are not.

Let me tell you what I use:

Dimensions:

*=aggr(if(rank(aggr(sum({<Artikelgroep3={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}, Maand={"<= $(=num(Maand))"}, Jaar={$(=only(Jaar))}>}OmzetBedrag),KlantNaam))<=3,KlantNaam),KlantNaam)

(this is to select the top three customers)

*artikelgroep3

*artikelnr

*EENHPR

My expressions to show the Sales (omzet)

=sum({<Artikelgroep3={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}, Maand={"<= $(=num(Maand))"}, Jaar={$(=only(Jaar))}>}OmzetBedrag)

My expression to show the Tons (tonnage)

=sum(aggr(sum({<Artikelgroep3={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}, Maand={"<= $(=num(Maand))"}, Jaar={$(=only(Jaar))}>}OmzetBedrag)

/

max(EENHPR), KlantNaam, Artikelgroep3, Artikelnr, Artikelomschrijving, EENHPR)

)

Can you please help me out?

Thank you!

Zipke

[Gysbert_Wassenaar]

Try adding a nodistinct to the aggr:

sum(aggr(nodistinct sum({<Artikelgroep3...