topic Re: Dynamic customer retention rate in QlikView

Dynamic customer retention rate

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: Dynamic customer retention rate

andrey_krylov — Wed, 24 Oct 2018 17:22:07 GMT

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)

Re: Dynamic customer retention rate

Anonymous — Thu, 25 Oct 2018 14:23:30 GMT

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

Re: Dynamic customer retention rate

andrey_krylov — Thu, 25 Oct 2018 14:42:33 GMT

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.

Re: Dynamic customer retention rate

Anonymous — Mon, 05 Nov 2018 10:00:09 GMT

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.

Re: Dynamic customer retention rate

andrey_krylov — Mon, 05 Nov 2018 20:40:00 GMT

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 {$< OrderDate={">=$(=date(AddYears(Max(OrderDate),-2)))<=$(=date(AddYears(Max(OrderDate),-1)))"} >} CLIENTID)

so I suppose there must be something else