Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
Could you please help me with the following challenge?
I have the following data (number of clients) in a pivot table in Qlikview:
#`Clients | Year client purchased item: | 2009 | 2010 | 2011 | 2012 |
---|---|---|---|---|---|
Year client registered: | |||||
From 2009 | 2.537 | 1.485 | 894 | 572 | |
5From 2010 | 12.195 | 4.032 | 2.184 | ||
From 2011 | 18.163 | 3.555 | |||
From 2012 | 10.887 | ||||
Now I would like to calculate the survival rate:
Survival rate | Year client purchased item: | 2009 | 2010 | 2011 | 2012 |
---|---|---|---|---|---|
Year client registered: | |||||
From 2009 | 100% | 59% | 35% | 23% | |
5From 2010 | 100% | 33% | 18% | ||
From 2011 | 100% | 20% | |||
From 2012 | 100% | ||||
The survival rate is calculated by dividing the number of clients in a particular year by the number of clients in the registration year.
For example: in 2011 there were 894 clients active who registered themselves in 2009. In total 2.537 persons registered themselves in 2009. 894/2.537 = 35%
Anyone any idea? All help is greatly appreciated!!
Best regards,
Dorine
Please replace with your script with attached script and change your expression to below:
=textCount (DISTINCT EMAIL) / textCount (DISTINCT TOTAL <InsertYear> IF(Year=MinYear, EMAIL))
Thank you for you quick response!
I copied your code but it is not working completely. Tomorrow morning I will dive deeper into your code to see what you changed. Hopefully I understand and make it work. I will let you know.
can you please post your sample source data with small data so I can help you more.
Dear Dathu,
Your answer was almost right but instead of MinYear I had to use InsertYear. It works perfectly. Thank you!!!!
Best regards,
Dorine