Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr Function in Pivot Table

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:

error loading image

This are the amounts that most be show:

error loading image

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

4 Replies
Not applicable
Author

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>
Not applicable
Author

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
Contributor III
Contributor III

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


Buena suerte

Not applicable
Author

tried already... no luck! Sad

Another way??

thxs!