Lost Customers using Set Analysis & Rolling Period & Sales Above given amount

Mon, 28 Nov 2011 13:22:32 GMT

Hello All,

I'm new in Qlik View (only 2weeks and without any experience in SQL or Database Programing). I'm trying toprepare a Dashboard for my project which includes the determination andevaluation of Lost-Won customers between a given period.

In principle, the definition ofthe Lost-Won customers (as has been expressed in the community in severalpost) can be summarized as following:

Lost Customers: Customers withsales in Period 1 and without sales in Period 2 (with Period 2 more recent intime than Period 1)

Won Customers: Customers withsales in Period 2 and without sales in Period 1 (with Period 2 more recent intime than Period 1)

In my database, we have two extra restrictions:

1.- Customer without sales donot show up in the database

2.- We can have customer withaggregated negative sales in the given period (returns, credit notes,etc) so ifthis is the case, it has to be considered as non customer or just like having no sales.

The restriction 1 implies (as I understand) I must use the Indirect Set Analysis (E()) in order to select themissing customers in the given period and perform the Intersection with theother period.

The restriction 2 implies thatI must aggregated the sales of each customers in the given period and filterthose with sales below or equal zero.

I have reviewed and checkedmost of the post regarding lost customers and set analysis, and I have to saythat for a newbie the SET Analysis is very complicated. After several days, I have prepared the following expressions that give me a very good determinationof most of the requirements I have:

Customers with sales>XX between periods using Set Analysis

=count({$<Month=,Year=,Quarter=,YearMonth={'$(Lower_Limit)'},ID={"=sum({$<Month=,Year=,Quarter=,YearMonth={'$(Lower_Limit)'}>}Invoice)>0"}>}DISTINCTID)

Lost Customers between periods using Set Analysis and without considering AggregatedSales

=Count({$<Month=,Year=,Quarter=,YearMonth=,YearMonth={'$(Lower_Limit)'},ID=E({$<Month=,Year=,Quarter=,YearMonth={'$(Upper_Limit)'}>})>}DISTINCT ID)

Won Customers betweenperiods using Set Analysis and without considering Aggregated Sales

=Count({$<Month=,Year=,Quarter=,YearMonth=,YearMonth={'$(Upper_Limit)'},ID=E({$<Month=,Year=,Quarter=,YearMonth={'$(Lower_Limit)'}>})>}DISTINCT ID)

I used two variables (Upper_Limit and Lower_Limit) that depend onthe rolling period that the user wants to use for the analysis (i.e. if theuser wants to check determine the Won-Lost customers in the last 3 month andthe selected month Mar 2011: the Upper Period = Jan -Mar 2011 and the LowerPeriod= Oct-Dec 2010. LCP is -3)

The variable definitions are:

Lower Limit ='<='&date(addmonths(date(monthStart(MAX_Date)),LCP), 'MMM-YYYY')&'>='&date(addmonths(date(monthStart(MAX_Date)),LCP2+1), 'MMM-YYYY')

Upper Limit ='<='&date(addmonths(date(monthStart(MAX_Date)),0), 'MMM-YYYY')&'>='&date(addmonths(date(monthStart(MAX_Date)),LCP+1), 'MMM-YYYY')

The main problem is that I cancalculate the Lost- Won Customer with no problem with no consideration of thesales, but when I try to include the filter or the consideration of sales, theoutput of the expression are no ID or all IDs. I'm using the followingexpression for this calculation:

Lost Customers withSales consideration

=count({$<Month=,Year=,Quarter=,YearMonth={'$(Lower_Limit)'},ID=E({"=sum({$<Month=,Year=,Quarter=,YearMonth={'$(Upper_Limit)'}>}Invoice)>0"})>}DISTINCTID)

With this expression I have as result 0 ID. If I change the E for P then I have all the IDs. I have triedalmost everything that I could think, but with no result. I have prepared afile as example with simple data to be used to check and validate theexpressions.

I have created two bookmarks inthe Qlik file:

BM01= Lost / Won Customerswithout sales considerations

Lost and Won ID between Q1-2011 vs Q4-2011: Lost ID (9403& 9404) Won ID (7701)

Doesn't have salesconsideration (i.e. all ID has positive sales in the periods)

BM02= Lost / Won Customers withsales considerations

Won and Lost IDs between twogiven period (Sep-Aug-Jul 2011) vs. (Jun-May-Apr2011)

Result from currentexpressions: Lost ID (9401 & 9402)

Missing lost ID due salesconsiderations (2505) (in the upper period do not has positive sales and itshould be considered as lost customer)

I'm using the personal editionof QlikView 10. So I cannot open any Qlik file from other user, so please postyour answers and expression or into a TXT file so I can used it after.

PS: I'm also attaching the excel file with the data in order to facilitate the analysis and validation ofthe results and expressions

Re: Lost Customers using Set Analysis & Rolling Period & Sales Above given amount

Tue, 29 Nov 2011 08:27:21 GMT

Hello all,

I think I got it, at the end I have created a union between the exclusion set and the set with the sales conditions.

The final expression for Lost Customers in a user defined period is:

=count({$<Month=,Year=,Quarter=,YearMonth={'$(Lower_Limit)'},ID=E({$<Month=,Year=,Quarter=,YearMonth={'$(Upper_Limit)'}>})+{"=sum({$<Month=,Year=,Quarter=,YearMonth={'$(Upper_Limit)'}>}Invoice)<=0"}>}DISTINCT ID)

Where:

E({$<Month=,Year=,Quarter=,YearMonth={'$(Upper_Limit)'}>})

Represent the ID that do not have any transaction in the upper period, and:

+{"=sum({$<Month=,Year=,Quarter=,YearMonth={'$(Upper_Limit)'}>}Invoice)<=0"}

Represent the union with the ID with sales equal or below zero.

I have tried in the Qlikview file and it works perfectly.

topic Lost Customers using Set Analysis & Rolling Period & Sales Above given amount in QlikView

Lost Customers using Set Analysis & Rolling Period & Sales Above given amount

Re: Lost Customers using Set Analysis & Rolling Period & Sales Above given amount