Multiple Count( IF statements

Thu, 20 May 2010 17:22:48 GMT

My data has the following structure for transactions:

ID Customer Item Date

I am developing a graph that analyses how many buyers are active, how many are lapsed and how many were reinstated.

For the active buyers I am using the following statement in the expression of a bar chart:

=COUNT(DISTINCT IF(Inmonth(Date,now(),-1),Customer,null()))

For the lapsed ones I would like to select all that did not buy in the last three months, but I can't figure out how.

For the reinstated ones I would like to count how many customers have purchased in the last month, although they had not purchased anything in the previous 3 months. I just can't find the way to do this either.

Any suggestions?

Multiple Count( IF statements

jedgson — Thu, 20 May 2010 17:38:48 GMT

You could create a flag in the Script

Temp_Map:Mapping LoadCustomer,max(Date)from DataSourceGroup By Customer;

LoadID,Customer,Item,Date,if(applymap('Temp_Map', Customer) >= addmonths(today(),-3),1,0) as PurchasedInLast3MonthsFlagfrom DataSource;

You can then use then use the flag in the chart

topic Multiple Count( IF statements in QlikView

Multiple Count( IF statements

Multiple Count( IF statements