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

[#1]

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.

[#2]

Hi dcroft,

Please, look into attachment.

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

[#3]

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

[#4]

Hi,

Please, take a look into attachment.

Kind regards,

Janusz

[dvasseur]

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

[dvasseur]

Sorry, didn't see Janusz new reply