Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculation between columns

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:

#`ClientsYear client purchased item:2009201020112012
Year client registered:
From 20092.5371.485894572
5From 201012.1954.0322.184
From 201118.1633.555
From 201210.887

Now I would like to calculate the survival rate:

Survival rateYear client purchased item:2009201020112012
Year client registered:
From 2009100%59%35%23%
5From 2010100%33%18%
From 2011100%20%
From 2012100%

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

13 Replies
Not applicable
Author

can you please post source data ?

Not applicable
Author

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.

Not applicable
Author

Something like this is maby simpler?

Cheers!

Not applicable
Author

Unfortunately, i use the personal edition. So i cannot open your qvw file 😞

Could you set the script in a post?

Not applicable
Author

Martin, thank you very much for you reaction. Is it possible to post the code in the post?

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Please post your sample source data so that I can help you which method is works for you.

Not applicable
Author

I read your mind and was just doing that 🙂