Problem with partial sums

[#1]

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

[marcus_sommer]

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

[#3]

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 😕

[marcus_sommer]

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

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

[#5]

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!

[marcus_sommer]

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

[#7]

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

[marcus_sommer]

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