topic Re: Problem with partial sums in QlikView

Problem with partial sums

Mon, 13 Oct 2014 12:01:38 GMT

Hello,

In my pivot table, I have 3 dimensions: representative, cutomer and order number.

For the dimension "order number", I'm calcultating the average of the different quotes.

For the dimension "customer, I'm calculating the average of the different orders.
And finally for the dimension "representative", I need to get the average of the different customers.

For the first two dimension, it works perfectly. But when it comes to the las dimension. Instead of taking the average of the customers, it takes the average of the orders, which doesn't give the same value as if I calculate it manually.

Here is the expression I use:

if(Dimensionality()=3, aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS),

if(Dimensionality()=2, avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS)),

if(Dimensionality()=1, avg(aggr(avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS)), JD_REF_CLIENT))

where:

JD_NDEVIS: order number

JD_REF_CLIENT: customer

JD_PX_TOTAL: value of the quote

JD_CODUNIQ: number of the quote

How do you think I could get to my result?

Thanks a lot

Re: Problem with partial sums

marcus_sommer — Mon, 13 Oct 2014 15:22:21 GMT

Maybe there is an aggr() too much and "JD_REF_CLIENT" should be in the inner aggr(), too. Also sometimes need the aggr() a NODISTINCT addition then per default calculates aggr() on a distinct level.

- Marcus

Re: Problem with partial sums

Tue, 14 Oct 2014 07:57:55 GMT

Do you mean like this:

if(Dimensionality()=3, aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS),

if(Dimensionality()=2, avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS)),

if(Dimensionality()=1, sum(aggr(avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS, JD_REF_CLIENT)), JD_NDEVIS, JD_REF_CLIENT))

It still doesn't change 😕

Re: Problem with partial sums

marcus_sommer — Tue, 14 Oct 2014 09:16:55 GMT

It's only guessing then I don't know your datamodell well enough:

if(Dimensionality()=1, avg(aggr(sum(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS)), JD_REF_CLIENT))

if(Dimensionality()=1, avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS, JD_REF_CLIENT))

if(Dimensionality()=1, avg(NODISTINCT aggr(avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS)), JD_REF_CLIENT))

Have a look here how aggr() worked:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/07/aggr

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-used

http://community.qlik.com/blogs/qlikviewdesignblog/2013/08/06/it-s-all-aggregations

http://community.qlik.com/blogs/qlikviewdesignblog/2014/05/19/function-classes

Also helpful could be:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

- Marcus

Re: Re: Problem with partial sums

Tue, 14 Oct 2014 09:31:21 GMT

The first one doens't help, and the second one still calculates the average on the number of orders and not on the numbers of customers 😕

For the third one, the nodistinct expression is not recognised by qlikview

Here are the files, if it is easier for you

Thanks!

Re: Problem with partial sums

marcus_sommer — Tue, 14 Oct 2014 09:42:08 GMT

Yes my mistake, nodistinct belonged to aggr not to avg:

if(Dimensionality()=1, avg(aggr(NODISTINCT avg(aggr(sum(JD_PX_TOTAL)/count(JD_CODUNIQ), JD_NDEVIS)), JD_REF_CLIENT))

I think it's more a logical issue. Is the inner aggr-part from your original expression the right basis to extend or nested in further part or not?

- Marcus

Re: Problem with partial sums

Tue, 14 Oct 2014 09:49:21 GMT

Not sure if I understand 😕

Actually, the first aggr part should calculate the average value per customer and the second one (the outside one) should calculate the average value per representative. So adding all the customer values and dividing them by the number of customers

Re: Problem with partial sums

marcus_sommer — Tue, 14 Oct 2014 10:56:39 GMT

If one of my expressions gives a wrong result I try to understand what has the expression calculated. If I know why is the result how it is (most through splitting the expressions in parts and/or calculate it manually) I could go further to modify the expression - if I don't understand the result then my actions are only trial and error ...

I could imagine that the in aggr() nested aggr() isn't the right way - maybe you need an expression like this:

if(..., aggr() / aggr() ...

Have a look on the above links, especially "when you don't use aggr ..." and "which average...".

- Marcus