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

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

Mon, 04 Jan 2010 20:35:32 GMT

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

Mon, 04 Jan 2010 20:48:11 GMT

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

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

Mon, 04 Jan 2010 21:01:49 GMT

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

Mon, 04 Jan 2010 21:26:18 GMT

Hi,

Please, take a look into attachment.

Kind regards,

Janusz

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

dvasseur — Mon, 04 Jan 2010 21:36:54 GMT

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

dvasseur — Mon, 04 Jan 2010 21:40:51 GMT

Sorry, didn't see Janusz new reply