Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Dynamic customer retention rate

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 ?

5 Replies
andrey_krylov
Specialist
Specialist

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
Not applicable
Author

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
Specialist
Specialist

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
Not applicable
Author

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
Specialist
Specialist

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