topic Re: partial sums pivot table not shown in QlikView

partial sums pivot table not shown

Tue, 26 Mar 2013 14:29:50 GMT

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

Re: partial sums pivot table not shown

Gysbert_Wassenaar — Tue, 26 Mar 2013 14:38:58 GMT

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

=(sum(Sales13))

max(EENHPR)

Re: partial sums pivot table not shown

Tue, 26 Mar 2013 14:43:40 GMT

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..

Re: partial sums pivot table not shown

alexandros17 — Tue, 26 Mar 2013 14:44:21 GMT

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

Re: partial sums pivot table not shown

Tue, 26 Mar 2013 14:46:51 GMT

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.

Re: partial sums pivot table not shown

Gysbert_Wassenaar — Tue, 26 Mar 2013 14:56:39 GMT

Ah, right. Need to aggregate that. Try:

=sum(aggr((sum(Sales13))

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

Re: partial sums pivot table not shown

Tue, 26 Mar 2013 15:14:44 GMT

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

Re: partial sums pivot table not shown

Gysbert_Wassenaar — Tue, 26 Mar 2013 15:23:26 GMT

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

Re: partial sums pivot table not shown

Thu, 28 Mar 2013 09:31:54 GMT

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

Re: partial sums pivot table not shown

Gysbert_Wassenaar — Fri, 29 Mar 2013 18:45:57 GMT

Try adding a nodistinct to the aggr:

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

Re: partial sums pivot table not shown

Tue, 02 Apr 2013 06:35:15 GMT

Dear,

The nodistinct does not change anything.

At the moment I have the correct calculation for the selected year and month, but the year-1 is giving me the wrong calculation.

Is it possible that it has got something to do with my calculated dimension? Because in the calculated dimension I only talk about only(Year) and not about only(Year-1)

The calculated dimension is:

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

and the expression for my year-1 is

=sum(aggr(nodistinct sum({<Artikelgroep3={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}, Jaar={$(=only(Jaar-1))}>}OmzetBedrag)
/
max(EENHPR), KlantNaam, Artikelgroep3, Artikelnr, Artikelomschrijving, EENHPR)
)

(with or without the nodistinct does not change anything)

Since only the year-1 is showing me somehting weird 641,14€/223 = 0€??? I think it must have got something to do with the years..?

Thank you

Re: partial sums pivot table not shown

Gysbert_Wassenaar — Tue, 02 Apr 2013 08:43:40 GMT

Try moving the -1 out of the only function:

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

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

)

Re: partial sums pivot table not shown

Tue, 02 Apr 2013 10:09:24 GMT

Yer I also thought that was the problem, but that did not change anything so I put it back to only(Year-1), it's the same result unfortunately..