Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi,
Please, take a look into attachment.
Kind regards,
Janusz
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
Sorry, didn't see Janusz new reply