topic Aggr Function in Pivot Table in QlikView

Aggr Function in Pivot Table

Wed, 17 Mar 2010 15:45:25 GMT

Hi everyone,

Starting:

I need to compare sales vs account receivable in a pivot table, sales has 2 dates: emision and due date. The comparison is with the due date, so i have the month as a dimession. So far, no problem.

Everything gets complicated when because in the same pivot table I need the sales at the emision date.So, I've used the aggr function as a dimenssion:

aggr(sum(SALES.tot_neto),SALES.Emis_Month)

The result it's a hyphen (-) in most of the month:

This are the amounts that most be show:

I guess this is happening because the main dimenssion (month of the due date) it's diferent to the one in the aggr function (month of the emision date)

How can i solve this? what am i doing wrong?

Thxs in advance!!

Aggr Function in Pivot Table

Thu, 18 Mar 2010 08:29:03 GMT

Just a hint here, when you use the AGGR function in a table/chart you will get unpredictable results if you do not aggregate over the existing dimensions as well as the additional ones you want. This is because the AGGR function ignores any dimensions currently in the table.

In your example it means that the AGGR function should aggregate over Ano, Vendedor & MES as well as Emis_Month so your expression should look like:

=AGGR ( SUM (SALES.tot_neto ) , ANO , VENDEDOR , MES , SALES.Emis_Month )
Of course, I'm guessing at your dimension names and just using the labels you have in your pivot, so you should replace those above with the actual field names in use.
Hope it helps,</body>

Aggr Function in Pivot Table

Thu, 18 Mar 2010 13:13:46 GMT

Hi Nigel, thxs for your response...

I still quite not understand the why about aggregate the dimensions of the pivot table to the aggr fucntion but I gave it a try and it did not work.

The hyphens appeared in all the column.

Any other idea??

Thxs in advance

Aggr Function in Pivot Table

mbernales — Thu, 18 Mar 2010 14:01:08 GMT

Try with aggr(NODISTINCT sum(SALES.tot_neto),SALES.Emis_Month)..

Buena suerte

Aggr Function in Pivot Table

Thu, 18 Mar 2010 14:42:22 GMT

tried already... no luck!

Another way??

thxs!