Complex set analysis

[#1]

Hi

I need help with an advanced set analysis.

I want to show customers that made their first order 2 month from the month that is selected. The customer must also had made at least 2 orders within this period, from the month selected and to 2 months back. This Set analysis doesnt work. Is it possible to solve this without using If?

Sum({$<CustomerId={"=Aggr(Sum({$<YearMonth={'>=$(=Date(AddMonths(Max(YearMonth),-2), 'YYYY-MM'))<=$(=Max(YearMonth))'}>}OrderCounter),CustomerId)>1"}, FirstOrder={"$(=Date(AddMonths(Max(YearMonth),-2), 'YYYY-MM'))"}, YearMonth>}CustomerCounter)

Thanks.

[#2]

Hi,

can you just post Sample qvw with the Data and Sample Output you need.

[RedSky001]

I'm not sure how you'd do this all within set analysis.  I came up with the following expression, obviously I'm using an If statement but if it works...

=if( AGGR( min(_MonthSerial) ,Customer) >=$(=Max(_MonthSerial)-1) and

AGGR( min(_MonthSerial) ,Customer) <=$(=Max(_MonthSerial)),1)

See the attached example.

[calvindk]

Make set analysis for the customers that made first order 2 months prior,

Make set analysis of customers that made minimum 2 orders in prior 2 months,

Take the intersection of those 2 set analysis.

[#5]

Thanks for all the response.

Anders, you mean something like this? Cant get it to work. Maybe I should use an if-statement.

Sum(

{$

<FirstOrder={"$(=Date(AddMonths(Max(YearMonth),-2), 'YYYY-MM'))"}, YearMonth>

*

<CustomerId={"=Aggr(Sum({$<YearMonth={">=$(=Date(AddMonths(Max(YearMonth),-2), 'YYYY-MM'))<=$(=Max(YearMonth))"}>}OrderCounter),CustomerId)>1"}>

}

CustomerCounter)

[calvindk]

Yes something like that. Do the 2 sets work individually? It looks like the syntax is a bit off.

But then again, and if sentence will work, its just slower.

If you have a lot of data or feel the performance take a hit, then work some more on intersection, else just go with if?

Best wishes

[#7]

There's something wrong with the second set analysis. Well, I will use If-statement. Thanks anyway.