5 Replies Latest reply: Jan 4, 2010 4:40 PM by David Vasseur

# Not sure if I can use the AGGR function for this problem

Hi,

I have a table with order details for individuals

The main fields for this problem are

Person Id

Order Date

I need to be able to count distinct Person Id where that Person has placed more than one Order in the last 12 months, for example using the data below, the answer would be 2

Person Order Date

123 03-jan-2009

123 06-jan-2009

345 07-jan-2009

345 08-jan-2009

456 02-jan-2009

567 11-feb-2009

567 19-jun-2009

I was wondering if I could use the AGGR function to do this? I never used it before - any advice would be most welcome.

• ###### Not sure if I can use the AGGR function for this problem

Hi dcroft,

You don't have to use AGGR function to achieve your goal.

For the condition "in the last 12 months" you can use set analysis in the COUNT function.

Kind regards,

Janusz

• ###### Not sure if I can use the AGGR function for this problem

Hi,

Thanks for getting back to me.

Sorry but I don't understand - that seems to give a person id along with a count if they have placed at least one order in the qualifying period.

So for example, Person 123 should not be included in the count.

Is it possible you could give me a bit more detail?

Thanks,

David

• ###### Not sure if I can use the AGGR function for this problem

Hi,

Please, take a look into attachment.

Kind regards,

Janusz

• ###### Not sure if I can use the AGGR function for this problem

Hi David,

It may not be the best way to do it but it seems to work:

`=Sum(Aggr(If(Count({<DATE = {">=\$(=AddMonths(Today(), -12))"}>} PERSONID)>1, 1), PERSONID))`

HTH,

David

• ###### Not sure if I can use the AGGR function for this problem

Sorry, didn't see Janusz new reply