Help in expression FOR LOST CUSTOMERS

[ali_hijazi]

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

[swuehl]

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

[ali_hijazi]

INDEED

[ali_hijazi]

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

[stevedark]

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?

[ali_hijazi]

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

Or

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?

[stevedark]

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

[ali_hijazi]

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

[stevedark]

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.

[effinty2112]

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