Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

tasnimdaghari
New Contributor II

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
Valued Contributor

Re: Dynamic customer retention rate

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)

tasnimdaghari
New Contributor II

Re: Dynamic customer retention rate

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
Valued Contributor

Re: Dynamic customer retention rate

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.

tasnimdaghari
New Contributor II

Re: Dynamic customer retention rate

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
Valued Contributor

Re: Dynamic customer retention rate

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