topic Re: Complex set analysis in QlikView

Complex set analysis

Thu, 04 Jul 2013 11:09:24 GMT

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.

Re: Complex set analysis

Thu, 04 Jul 2013 11:40:46 GMT

Hi,

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

Re: Complex set analysis

RedSky001 — Thu, 04 Jul 2013 13:35:58 GMT

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.

Re: Complex set analysis

calvindk — Thu, 04 Jul 2013 14:01:06 GMT

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.

Re: Complex set analysis

Thu, 04 Jul 2013 14:24:30 GMT

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)

Re: Complex set analysis

calvindk — Thu, 04 Jul 2013 14:57:17 GMT

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

Re: Complex set analysis

Fri, 05 Jul 2013 13:51:25 GMT

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