topic Re: Problems with Aggr() in QlikView

Problems with Aggr()

ecabanas — Thu, 31 Jan 2013 12:53:03 GMT

I'm doing a pivot with that and it works perfectly, but did not add correctly the total, I tryied several combinations with aggr, but it doesn't work, HELPPP PLEASEEE! 😉

=Sum({<SALESID =p(),DES_SUB1= >} IMPORTBRUT+CTEFISICO+CAMPANA)

Eduard

Re: Problems with Aggr()

er_mohit — Thu, 31 Jan 2013 12:55:51 GMT

=Sum({<SALESID =p(),DES_SUB1= >}Total IMPORTBRUT+CTEFISICO+CAMPANA)

Re: Problems with Aggr()

swuehl — Thu, 31 Jan 2013 12:58:05 GMT

That's hard to answer without knowing your setting. If you want to do a sum-of-rows in a pivot using aggr(), try something like

=sum( aggr( Sum({<SALESID =p(),DES_SUB1= >} IMPORTBRUT+CTEFISICO+CAMPANA), YOURTABLEDIMENSIONFIELDS ))

replace YOURTABLEDIMENSIONFIELDS with the list of your table dimensions (fields only, no calculated dims).

edit: There is an chapter in the HELP about this, please refer to it for more detail.

And you may need to also use the set expression in the outer aggregation function:

=sum( {<SALESID =p(),DES_SUB1= >}

aggr( Sum({<SALESID =p(),DES_SUB1= >} IMPORTBRUT+CTEFISICO+CAMPANA), YOURTABLEDIMENSIONFIELDS )

)

Re: Problems with Aggr()

ecabanas — Thu, 31 Jan 2013 15:42:55 GMT

Still error:

My main dimension is SalesID

The code for Mgn formula is:

=Sum({<SALESID =p(),DES_SUB1= >}Total IMPORTBRUT+CTEFISICO+CAMPAÑA)

With the aggr did not works gggrrrrrr!! 😞

and the result in the table is the following

Re: Problems with Aggr()

Oleg_Troyansky — Thu, 31 Jan 2013 16:15:44 GMT

A few ideas to explore:

1. I suspect that one of the three fields tht you add up, might have a NULL value. try using RangeSum() instead of a simple addition:

sum( {...} rangesum(A,B,C))

2. SALESID is your dimension, and at the same time you are using it in Set Analysis - that's not always a good combination.

3. You have an additional condition on DES_SUB1 (disregarding user selections). Are you applying the same condition on all the columns in your chart? If you don't then the results could look illogical - some of the columns may or may not disregard used selections, while the total always disregards ... Maybe that's what causes the confusion?

Cheers,

Oleg Troyansky