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

[ali_hijazi]

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

[stevedark]

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

[ali_hijazi]

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)