Aggr() and Only() Functions

[IAMDV]

Dear All,

I had read the QV documentation and still confused with Aggr() and Only() functions. Can someone simply the explanation for these two functions? Or you can redirect me to a previous post...

Cheers!

[#2]

Hi DV,

Here a good explaination for AGGR function : http://community.qlik.com/forums/p/23009/88474.aspx

Hope this helps ;-))

Kindly,

[#3]

The aggr function was really hard for me to learn but once I finally got it it's been one of the most useful things. Some examples can be seen below:

Imagine one customer can have many invoices, and I want to know how many customers have an invoice that is more than 180 days overdue. Also, I want to show this grouped by city, so the only dimension I want to show is city. I would use the expression below:

=count
(distinct //don't want to double count customers
 aggr
 (
 if([Invoice Balance]>0, //means they owe money on that invoice
 if([Invoice Due Date]<today()-180, //only want invoices that have been due for over 180 days
 [Customer Number]
 )
 )
 ,[Customer Number],[Invoice Number] //these are the dimensions that I would need to show if I wasn't using aggr to make this work
 )
)
)

It helps me to think about it in terms of "what dimensions would I need to show here to get the results I want". If some of those dimensions that you would need to show to get your results ones that you don't actually want displayed (like invoice number and customer number in the example above) then you need to use aggr. Hopefully that made any sense at all.

[#4 · Accepted solution]

Gregory,

the link is not working.  I saw another post which explains about aggr.

http://community.qlik.com/message/125490

[IAMDV]

Thank you. Although this is very old post, this should help other users who access this thread.

Thanks for taking time to update.

Cheers - DV