Calculation between columns

[#1]

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

[#2 · Accepted solution]

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

[#3]

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.

[#4]

can you please post your sample source data with small data so I can help you more.

[#5]

Dear Dathu,

Your answer was almost right but instead of MinYear I had to use InsertYear. It works perfectly. Thank you!!!!

Best regards,

Dorine