Dynamic customer retention rate

[Anonymous]

I need an expert opinion on this matter, I need to calculate the customer retention rate with a dynamic date selected on the chart and then rollback with a year and compare it to the previous year, for exemple  if i select 10/23/2018 then date selection would be from 10/23/2017 to 10/23/2018 and get the rate pourcentage between 10/23/2017-10/23/2018 and 10/23/2017-10/23/2016. so i tried to use this expression :

Sum(distinct{$<

CLIENTID=p({<OrderDate={"

>=$(=date(AddYears(date(GetFieldSelections(OrderDate)),-2)))

<=$(=date(AddYears(date(GetFieldSelections(OrderDate)),-1)))"} >}CLIENTID)

*

p({<OrderDate={">=$(=date(AddYears(date(GetFieldSelections(OrderDate)),-1)))

<=$(=date(GetFieldSelections([OrderDate])))"} >}CLIENTID)

>}CLIENTID)

but it doesn't, is this the right approach? if not how to proceed ?

[andrey_krylov]

Hi Tasnim. Try this

Count(distinct{$< CLIENTID= p({<OrderDate={">=$(=date(AddYears(Max(OrderDate),-2)))<=$(=date(AddYears(Max(OrderDate),-1)))"} >} CLIENTID) * p({<OrderDate={">=$(=date(AddYears(Max(OrderDate),-1)))<=$(=date(Max([OrderDate])))"} >} CLIENTID) >}CLIENTID)

/

Count(distinct {$< OrderDate={">=$(=date(AddYears(Max(OrderDate),-2)))<=$(=date(AddYears(Max(OrderDate),-1)))"} >} CLIENTID)

[Anonymous]

Hi Andrey, Thank you for your quick response but this expression makes static selection , but I want to be able to choose the end date of my year on the char that's why i used the "GetFieldSelections" and from that variable rollback with a year

[andrey_krylov]

Hi, Tasnim. No, Max(OrderDate) is not static, it gets the max selected (available) value. I've used this statement to avoid situations when several OrderDates are selected and the expression would give an error. In other cases, when a single value is selected, it’s the same thing.

[Anonymous]

Hi again Andrey, this expression is working but not the right output , it only selects one month and same month last year and what I want is  this year and the year before, I tried tweaking the expression but still no result.

[andrey_krylov]

Hi Tasnim. Why do you think it selects the only one month? But.. maybe I misunderstood the issue and you need somthing different. You know, if you replace sum with count in your expression it will count the customers that are in both periods only

Count(distinct{$<

CLIENTID=p({<OrderDate={"

>=$(=date(AddYears(date(GetFieldSelections(OrderDate)),-2)))

<=$(=date(AddYears(date(GetFieldSelections(OrderDate)),-1)))"} >}CLIENTID)

*

p({<OrderDate={">=$(=date(AddYears(date(GetFieldSelections(OrderDate)),-1)))

<=$(=date(GetFieldSelections([OrderDate])))"} >}CLIENTID)

>}CLIENTID)

and that is the same that in my expression is highlighted

Count(distinct{$< CLIENTID= p({<OrderDate={">=$(=date(AddYears(Max(OrderDate),-2)))<=$(=date(AddYears(Max(OrderDate),-1)))"} >} CLIENTID) * p({<OrderDate={">=$(=date(AddYears(Max(OrderDate),-1)))<=$(=date(Max([OrderDate])))"} >} CLIENTID) >}CLIENTID)

/

Count(distinct {$< OrderDate={">=$(=date(AddYears(Max(OrderDate),-2)))<=$(=date(AddYears(Max(OrderDate),-1)))"} >} CLIENTID)

so I suppose there must be something else