topic Re: wrong total sum over column in QlikView

wrong total sum over column

Tue, 18 Feb 2014 13:15:02 GMT

Hello everybody,

i have an Pivot table with Prices of the actuale year "Preis", Prices of previous year "Preis_Vorjahr", the % change of the Price "relative Abweichung", the costs of the goods last year "Warenkosten VJ" and the calculated value with the Price cange "Warenkosten progn.".

The total sum of the calculated new Price column gives a difference of 0,02€.

Can somebody explain it or give me an example, how to do it right?

The column "Warenkosten progn." is wrong, the correct sum of the rows is 979,57 not 979,55

Thanks to all

Re: wrong total sum over column

alexandros17 — Tue, 18 Feb 2014 13:17:49 GMT

Probably is due to rounding, you loose decimal places tat when summed lead to different totals

Re: wrong total sum over column

Tue, 18 Feb 2014 13:23:46 GMT

thanks for the answer.

i have changed the decimals to 14 but the value is the same

0,00000000000000

84,33828000000000

0,00000000000000

161,45768484307600

0,00000000000000

168,67656000000000

565,10189695076600

0,00000000000000

Re: wrong total sum over column

alexandros17 — Tue, 18 Feb 2014 13:24:45 GMT

Add your example if you can

Re: wrong total sum over column

rbecher — Tue, 18 Feb 2014 13:32:33 GMT

The aggregated sum should be: 979,58

What is the expression of this field?

Re: wrong total sum over column

Tue, 18 Feb 2014 13:38:37 GMT

the value should by 979,58 thats correct.

The Expression of the column is:

"Warenkosten VJ"*"relative Abweichung")/100+"Warenkosten VJ"

include the other expressions

"Warenkosten VJ"

({<Jahr={$(=getfieldselections(Jahr)-1)}>} Lieferantenstatistik.Umsatz)

"relative Abweichung"

(Sum({<Jahr={$(=getfieldselections(Jahr))}>} ItemPosition.Preis_BE)/Sum({<Jahr={$(=getfieldselections(Jahr)-1)}>} ItemPosition.Preis_BE))-1)

Re: wrong total sum over column

Tue, 18 Feb 2014 13:55:02 GMT

Hi,

Try to use Num function for <relative Abweichung> and <main expression> as below:

Num(relative Abweichung exp, '#,##0.0##')

Num(("Warenkosten VJ" * "relative Abweichung")*100 + "Warenkosten VJ", '#,##0.0##')

Re: wrong total sum over column

rbecher — Tue, 18 Feb 2014 14:03:46 GMT

Could you attach an example please..

Re: Re: wrong total sum over column

Tue, 18 Feb 2014 14:19:53 GMT

i have made a short example of my solution, because the data in the original are not for public use. In my example i get the same, wrong values.

Re: Re: Re: wrong total sum over column

rbecher — Tue, 18 Feb 2014 15:27:55 GMT

Now I got it. The right aggregation expression is:

=sum(aggr(round(sum(Warenkosten_VJ)*(sum(Preis)/sum(Preis_Vorjahr)-1)/100+sum(Warenkosten_VJ),0.01),ID))

Re: Re: wrong total sum over column

Tue, 18 Feb 2014 15:38:01 GMT

cool, super, very good!

Re: wrong total sum over column

rbecher — Tue, 18 Feb 2014 15:43:46 GMT

..or this expression (simplified):

=sum(round(Warenkosten_VJ*((Preis/Preis_Vorjahr-1)/100+1),0.01))

Re: Re: wrong total sum over column

Wed, 19 Feb 2014 08:20:43 GMT

Hallo everybody. My solution now inlcudes the following statement:

sum(aggr(round(sum({<Jahr={$(=getfieldselections(Jahr)-1)}>} Lieferantenstatistik.Umsatz)*(Sum({<Jahr={$(=getfieldselections(Jahr))}>} ItemPosition.Preis_BE)/Sum({<Jahr={$(=getfieldselections(Jahr)-1)}>} ItemPosition.Preis_BE)-1)/100+sum({<Jahr={$(=getfieldselections(Jahr)-1)}>} Lieferantenstatistik.Umsatz),0.01),Warengruppen.Name_1,Referenztabelle_Warenkorb.Warengruppe,ItemHeader.Description,ItemPosition.LieferantenArtikelNr))

my Problem after getting the Expression from Ralf Becker was that i have had used variablenames instead of the expressions.