Help with aggr() function

Fri, 14 Oct 2011 11:12:12 GMT

Hi Everyone,

I find that the aggr function doesn't always work the way I think it will and unfortunately the documentation around this function is limited and only helpful for specific scenarios.

I am trying to count firstly the number of people selling an item in 2010 who had previously bought an item. I calculate this using the following:

=sum (
          if(
                    aggr(min( {< RECORD_TYPE={'BUYER'}, SALE_YEAR=>} SALE_START_DATE), CONTACT_ID)
                    <
                    aggr(min( {< RECORD_TYPE={'SELLER'}, SALE_YEAR={'2010'}>} SALE_START_DATE), CONTACT_ID)
                    ,1,0))

In English, I am trying to say that if the minimum sale_start_date where a contact_id was a buyer (irrespective of year) is less than the minimum sale_start_date in the sale_year of 2010 where a contact_id was a seller, then give me a 1. Summing these 1/0 flags should give me the number of contact_id's who sold in 2010 and had bought previously (either ealier in 2010 or earlier ever).

I am not confident that the number it is returning is correct. I can do this in a table with Contact_ID as the dimension and without the aggregation and I can generate the 1/0 flag, so this is the next step in dropping the Contact_ID from the table and aggregating up to a total level.

Any advice would be greatly appreciated!

Thanks

Gareth

topic Help with aggr() function in QlikView

Help with aggr() function