topic Re: How calculate recurrency in QlikView

How calculate recurrency

ecabanas — Fri, 29 Jan 2016 13:05:03 GMT

Hi guys,

I'm doing a table to calculate the cohort o customer recurrency . For example

The calculation is, how many customers return in the following months:

Many thank's guys

Re: How calculate recurrency

Anonymous — Fri, 29 Jan 2016 13:16:54 GMT

For First,

people bought in Jan : count(distinct Customer)

For 2nd & 3rd, like this?

Assuming your script:

month(FirstOrder) as Firstmonth

month(Date Order) as Ordermonth

=count({<Firstmonth={'1'}, Ordermonth={'>1'}>}distinct Customer)

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 13:17:31 GMT

Something like this may be:

=Count(DISTINCT {<Customer = p({<MonthYear = {"$(=MonthName(Min(MonthYear)))"}>})>}Customer)

Re: How calculate recurrency

ecabanas — Fri, 29 Jan 2016 13:31:36 GMT

Hi Sunny,

It's ok, but the example was to know january, but probably I did not explain correct.

I wanna know the guys who return to our site, Using a calendarmonth I wanna know dinamicaly, not harcoded, is ti possyble??

How many guys from FirstOrder was january has returned, but how many from february, march, etc???

did you understant, may be i did not explain correctly

Many thank's

Eduard

PS:Remember the paella 😉

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 13:37:13 GMT

Not sure I understand it still, but may be like the attached? Where you can select the MonthYear and it will show how many customers have bought in other Months?

Re: How calculate recurrency

ecabanas — Fri, 29 Jan 2016 13:47:51 GMT

Hi Sunny

We are going in the right way, but try to select february

I wanna know how many guys that his first order was in "February" Bough in march and followings...is not normal that if you did the first in february appears results in January...and the result in february has to be 1 not 2....

agghhhh it's a tricky calculation

thanks again

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 14:03:17 GMT

What about this?

Re: How calculate recurrency

Fri, 29 Jan 2016 15:08:07 GMT

Do you Master calendar in your data model and if yes, please add month no that hold 1,2,3 etc with your Month Name ascending order. Then use the below:

=Count(DISTINCT {<Customer = p({<MonthNo = {"$(=Min(MonthNo)"}>})>}Customer)

If you select any Month Name & Min(MonthNo) will give that date.

Re: How calculate recurrency

Fri, 29 Jan 2016 15:09:26 GMT

Please post more sample data to test all your scenarios.

Re: How calculate recurrency

ecabanas — Fri, 29 Jan 2016 15:47:35 GMT

Hi Sunny,

If I select february, there is one that first order was february (Customer C) but this guy did not returned in any month any the table says 1 in March!!

Many many thank's for your patience

Eduard

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 15:50:18 GMT

I see what you mean now. I will come back with an expression that will do that

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 16:29:48 GMT

Although there might be an easy way to do this, but I came up with this expression which seems to work except for some reason it hides the dimension when the value = 0. I think we need expert advice here from swuehl‌

I am sure he not only will get a simplified version of this expression, but will also resolve the other issue of hiding dimension.

Count(DISTINCT {<Customer = p({<Order = p({<MonthYear = {"$(=vVar)"}>})*p({<Order = {"=Month([Date Order]) = Month(FirstOrder)"}>})>})>}Order)

Best,

Sunny

Re: How calculate recurrency

swuehl — Fri, 29 Jan 2016 17:00:04 GMT

Sunny,

your solution seems to work for me, even showing dimensions with zero value in the expression.

Attached another approach.

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 17:04:05 GMT

I had a hidden column (Count(Customer)) which was letting me show the 0 months. I have disabled that, can you check now.

Re: How calculate recurrency

ecabanas — Fri, 29 Jan 2016 17:14:16 GMT

Hi Sunny,

I'm going to stress the function and I hope it will work in the real model

Crossing fingers!!!!

Many thank's for your help

Re: How calculate recurrency

sunny_talwar — Fri, 29 Jan 2016 17:17:47 GMT

Do look at Stefan's expression as well.