Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello got the following expression that is supposed to find by month the number of lost customers
it is counting the distinct customer_key where customer_key is excluded in 12-months-period-back but has invoices in dates prior to the 12-months-back-period with respect to each value in the dimension
SUM( AGGR(count
(
distinct
{
<
CUSTOMER_KEY=E(
{
<
TRANS_TYPE={'INVOICE'}
, MONTH=,YEAR=,QUARTER=//,
TRANS_DATE={"= TRANS_DATE >= $(vTwelve_Months_Back_Selected_Date_II)"}
,TRANS_DATE={"=TRANS_DATE <= $(vWon_Lost_Max_Date_II)"}
>
}CUSTOMER_KEY)
,CUSTOMER_KEY=P(
{
<
TRANS_TYPE={'INVOICE'}
, MONTH=,YEAR=,QUARTER=
,TRANS_DATE={"=TRANS_DATE< $(vTwelve_Months_Back_Selected_Date_II)"}
>
}IF(CUSTOMER_KEY)
>
} CUSTOMER_KEY),MONTH_YEAR))
However this expression is returning null
it only works if I define the above variables with = sign to return one specific value , however I need that the 12-monts-back period is calculated with respect to each month year of the dimension
gwassenaar robvoets maleksafa