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))
can you please post source data ?
Hi Dorine, I believe your requirement is very difficult to do on UI with expression. I proposed 2 methods in the script.
use either of one method fits with your model.
Something like this is maby simpler?
Cheers!
Unfortunately, i use the personal edition. So i cannot open your qvw file 😞
Could you set the script in a post?
Martin, thank you very much for you reaction. Is it possible to post the code in the post?
Hi Dorine, Please find the below script & expressions and data will on the excel file:
Script:
Data:
LOAD YEAR_REGISTERED,
Year,
Value,
IF(Previous(YEAR_REGISTERED)=YEAR_REGISTERED , Peek(Total_Value), Value) AS Total_Value // use this if you have aggregated data
FROM survial.xlsx (ooxml, embedded labels, table is Sheet1);
Left Join (Data)
LOAD YEAR_REGISTERED , Min(Year) AS MinYear // use this when you dont have aggregated data
Resident Data Group By YEAR_REGISTERED;
Pivot Table:
Method 1: using Total_Value filed
Dimension1: YEAR_REGISTERED
Dimension2: Year
Expression: sum(Value)/sum(Total_Value)
Method2: Using MinYear field
Dimension1: YEAR_REGISTERED
Dimension2: Year
Expression: Sum(Value)/sum(TOTAL <YEAR_REGISTERED> IF(Year=MinYear, Value,0) )
Thank you very much for your reply. Unfortunately, I don't really understand it.
The tables in my original post are pivot tables (so aggregated) based on an excel file. Should I put your code in the LOAD statement of the script? Or in the script of the expression?
I added my file. Hopefully you see what I do wrong.
Please post your sample source data so that I can help you which method is works for you.
I read your mind and was just doing that 🙂