Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with partial sums

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

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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