Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Complex set analysis


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)


6 Replies
Not applicable

Re: Complex set analysis


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

Contributor III

Re: Complex set analysis

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.

Contributor III

Re: Complex set analysis

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.

Not applicable

Re: Complex 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.



<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"}>



Contributor III

Re: Complex set analysis

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

Not applicable

Re: Complex set analysis

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