13 Replies Latest reply: Feb 13, 2014 2:19 PM by Dorine van Mullem-Ale

Calculation between columns

Dear all,

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

• Re: Calculation between columns

can you please post source data ?

• Re: Calculation between columns

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.

• Re: Calculation between columns

Unfortunately, i use the personal edition. So i cannot open your qvw file :-(

Could you set the script in a post?

• Re: Calculation between columns

Hi Dorine, Please find the below script & expressions and data will on the excel file:

Script:

Data:

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

• Re: Calculation between columns

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.

• Re: Re: Calculation between columns

=textCount (DISTINCT EMAIL) / textCount (DISTINCT TOTAL <InsertYear> IF(Year=MinYear, EMAIL))

• Re: Calculation between columns

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.

• Re: Calculation between columns

Dear Dathu,

Best regards,

Dorine

• Re: Calculation between columns

Something like this is maby simpler?

Cheers!

• Re: Calculation between columns

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