Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
can you just post Sample qvw with the Data and Sample Output you need.
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.
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.
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)
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
There's something wrong with the second set analysis. Well, I will use If-statement. Thanks anyway.