topic Re: Help in expression FOR LOST CUSTOMERS in QlikView

Help in expression FOR LOST CUSTOMERS

ali_hijazi — Mon, 08 Aug 2016 13:29:23 GMT

Hello

I want to get the count of lost customers;

they are the ones who had transactions before April 1st 2015 but after that date no more transactions are available for those customers:

I used the following expression:

note: vWon_Lost_Max_Date is 31/3/2016 and vTwelve_Months_Back_Selected_Date is 4/1/2015

count(distinct {

TRANS_TYPE={'INVOICE'},WEEK=,YEAR=,MONTH=,MONTH_NUM=,DAY=,QUARTER=,MONTH_DAY=,MONTH_YEAR=,WEEK_DAY=,TRANS_DATE={'<=$(vWon_Lost_Max_Date)'}

CUSTOMER_KEY={"=count({< TRANS_TYPE={'INVOICE'},WEEK=,YEAR=,MONTH=,MONTH_NUM=,DAY=,QUARTER=,MONTH_DAY=,MONTH_YEAR=,WEEK_DAY=,TRANS_DATE={'>=$(vTwelve_Months_Back_Selected_Date) <= $(vWon_Lost_Max_Date)'}>}CUSTOMER_KEY)>0"}

,TRANS_TYPE={'INVOICE'},WEEK=,YEAR=,MONTH=,MONTH_NUM=,DAY=,QUARTER=,MONTH_DAY=,MONTH_YEAR=,WEEK_DAY=,TRANS_DATE={'<=$(vWon_Lost_Max_Date)'}

}CUSTOMER_KEY)

the result of this expression is correct but when I put it in a pivot table with Dimension Customer_Key and Business_Unit_Name

the business unit name is always null

Same if I add Month_Year (from the calendar)

So with this expression I can only get result as numbers in a kpi but cannot have it aggregated by any dimension on a pivot table for example

below is the schema please advise gwassenaar‌

jp_bakhache‌ rodrigue.saade‌

Message was edited by: Ali Hijazi

Re: Help in expression

swuehl — Mon, 08 Aug 2016 14:59:58 GMT

Do you have Business Unit set for TRANS_TYPE = INVOICE in your fact table?

Re: Help in expression

ali_hijazi — Mon, 08 Aug 2016 15:19:34 GMT

INDEED

Re: Help in expression FOR LOST CUSTOMERS

ali_hijazi — Mon, 08 Aug 2016 19:54:17 GMT

The result of this expression is correct and when I filter by business unit I also correct result but when ever I add the business unit as dimension on a pivot table the value is always null

Same applies to month, or month_year dimension

Re: Help in expression FOR LOST CUSTOMERS

stevedark — Mon, 08 Aug 2016 21:03:06 GMT

Could it be that the association for business unit name is only for recent records (i.e. those that you are excluding). If any of the associations include date then this would be a distinct possibility?

Re: Help in expression FOR LOST CUSTOMERS

ali_hijazi — Wed, 10 Aug 2016 07:17:15 GMT

Lost customers can be calculated in 2 ways:

Suppose you select 2016 Jan and Feb

Then for Jan the lost customers are the ones who don't have invoice transactions between 1 Feb 2015 till 31 Jan 2016 but have at least one invoice from 31 Jan 2015 and backwards

And for Feb we count those who don't have transactions between 1 march 2015 till 28 Feb 2016

For Jan

The max invoice date for the customer is < 1 Feb 2015

And for Feb The max invoice date for the customer is < 1 march 2015

if I share a sample document, would please help me?

Re: Help in expression FOR LOST CUSTOMERS

stevedark — Wed, 10 Aug 2016 09:29:08 GMT

Hi Ali,

Sure, I can try and find some time to do this - but I can't promise when it will be.

Looking at your expression I would suggest you break it down into chunks, and use the aggr function.

First create a table that will allow you to check your logic and validate your numbers. Create a straight table, with CUSTOMER_KEY as the dimension. Then create columns for Old Transactions (e.g. before cut off date) and Recent Transactions (after cut off). You can then create a third column which is if(Old Transactions > 0 and Recent Transactions = 0 then 1 else 0) - rather than referencing the previous columns here re-use the expressions in the previous two columns.

What you should have then is a flag in each row in the third column which will indicate whether or not that customer should be counted or not. Make sure you have this working before you move on.

You will notice that the total on this third column will be 0 (as there will almost certainly be some old and some new transactions at a total level) rather than a count of customers. I would try fixing this up in a fourth column. The syntax for which is:

sum(aggr((... expression from column 3 ...), CUSTOMER_KEY))

What you should get is the same zeros and ones on each row, but the total should be a count of the customers who match the criteria.

Now through the magic of AGGR you will be able to take that expression from the fourth column and use it against other dimensions and still get the correct result. Google AGGR in QlikView to find out more about what it is doing here.

Hope that all makes sense and allows you to get to a working solution.

Cheers,
Steve

Re: Help in expression FOR LOST CUSTOMERS

ali_hijazi — Wed, 10 Aug 2016 10:01:05 GMT

the customer will be lost or not depending on the selection the user makes on year, and month

Re: Help in expression FOR LOST CUSTOMERS

stevedark — Wed, 10 Aug 2016 10:18:24 GMT

That's fine, just make sure that your two variables are set based on selection, e.g.

=Date(AddMonths(max(TRANS_DATE), -3), 'DD/MM/YYYY')

It is crucial that the variables return the date in the correct format that matches the 'natural' format of the date field. This will be the format the date appears in a list box if you don't change anything on the Number tab.

Breaking down into separate chunks to validate as I mentioned above will make your Set Analysis simpler (i.e. you don't need two sets in one expression).

The only other potential issue I can see in your code is that wildcard or partial matches in set analysis should be in double quotes rather than single.

Re: Help in expression FOR LOST CUSTOMERS

effinty2112 — Wed, 10 Aug 2016 11:15:12 GMT

Hi Ali,

Have you tried any thing along the lines of:

=Count({$<CUSTOMER_KEY = P({<TRANS_DATE = {"<01/05/2015"}>})>*<CUSTOMER_KEY = E({<TRANS_DATE = {">=01/05/2015"}>})>}DISTINCT CUSTOMER_KEY)

Good Luck

Andrew

Re: Help in expression FOR LOST CUSTOMERS

ali_hijazi — Wed, 10 Aug 2016 13:30:19 GMT

Yes I tried this

but the problem is that the transdate is dependant on the date value of the dimension

so the range for jan 2016 is feb 2015 till end of december 2015

for feb 2016 it is from Mar 2015 till end of Jan 2016 and so on

Re: Help in expression FOR LOST CUSTOMERS

stevedark — Wed, 10 Aug 2016 13:55:22 GMT

Hi Ali,

I now see your predicament! You will not be able to pass the dimension value into the Set Analysis in order to give different date ranges on different rows of the report.

Rather than having a date dimension you will need to create a separate expression for each month. Each expression can then have a different date range for it.

The neatest way of doing this is to put the code in a variable and pass parameters into the variable, giving an offset. I've done an article on passing parameters into variables you may find useful:

http://www.quickintelligence.co.uk/variables-parameters-load-script/

It's quite fiddly to do, but it should allow you to achieve what you want.

The first expression will be based on current month, so will have a parameter of 0 and you would need to use the AddMonths expression in the variable accordingly:

So, the expression would be something like:

$(vExpLapsedCustomers(0))

And then the set analysis would be:

TRANS_DATE={">=$(=AddMonths(MonthStart(Max(TRANS_DATE)), -3-$1))"}

And

TRANS_DATE={">=$(=AddMonths(MonthStart(Max(TRANS_DATE)), -9-$1))<$(=AddMonths(MonthStart(Max(TRANS_DATE)), -3-$1))"}

In both Set Analysis expressions the $1 will relate to the parameter (i.e. 0).

You can then create a second expression that will have 1 as a parameter, i.e.:

$(vExpLapsedCustomers(1))

Then repeat for as many months as you want to go back.

Hope that helps.

Steve

Re: Help in expression FOR LOST CUSTOMERS

ali_hijazi — Thu, 11 Aug 2016 11:53:08 GMT

I used the following variables in the expression that is available in the sample document I shared in my trial to get to the wanted results but all in vain

vWon_Lost_Max_Date

=max({1<TRANS_TYPE={'invoice*'},YEAR=$::YEAR,MONTH=$::MONTH,DAY=$::DAY,WEEK=$::WEEK,MONTH_YEAR=$::MONTH_YEAR,QUARTER=$::QUARTER,MONTH_NUM=$::MONTH_NUM,WEEK_DAY=$::WEEK_DAY>} TRANS_DATE)

vWon_Lost_Max_Date_II

MAX(AGGR(max({1<TRANS_TYPE={'invoice*'},YEAR=$::YEAR,MONTH=$::MONTH,DAY=$::DAY,WEEK=$::WEEK,MONTH_YEAR=$::MONTH_YEAR,QUARTER=$::QUARTER,MONTH_NUM=$::MONTH_NUM,WEEK_DAY=$::WEEK_DAY>} TRANS_DATE),MONTH_YEAR))

vTwelve_Months_Back_Selected_Date

=ADDMONTHS(DATE($(vWon_Lost_Max_Date)),-12)+1

vTwelve_Months_Back_Selected_Date_II

AGGR((ADDMONTHS(DATE(MAX(AGGR(max({1<TRANS_TYPE={'invoice*'},YEAR=$::YEAR,MONTH=$::MONTH,DAY=$::DAY,WEEK=$::WEEK,MONTH_YEAR=$::MONTH_YEAR,QUARTER=$::QUARTER,MONTH_NUM=$::MONTH_NUM,WEEK_DAY=$::WEEK_DAY>} TRANS_DATE),MONTH_YEAR))),-12)+1),MONTH_YEAR)