Aggr Function in Pivot Table

[#1]

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

[#2]

Hi

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>

[#3]

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

[mbernales]

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

Buena suerte

[#5]

tried already... no luck!

Another way??

thxs!