topic Re: Calculation between columns in QlikView

Calculation between columns

Wed, 12 Feb 2014 11:52:32 GMT

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

Re: Calculation between columns

Wed, 12 Feb 2014 13:35:09 GMT

can you please post source data ?

Re: Calculation between columns

Wed, 12 Feb 2014 14:29:49 GMT

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

Wed, 12 Feb 2014 15:06:53 GMT

Something like this is maby simpler?

Cheers!

Re: Calculation between columns

Wed, 12 Feb 2014 19:18:33 GMT

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

Wed, 12 Feb 2014 19:20:46 GMT

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

Re: Calculation between columns

Wed, 12 Feb 2014 19:26:29 GMT

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

Re: Calculation between columns

Wed, 12 Feb 2014 20:07:29 GMT

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: Calculation between columns

Wed, 12 Feb 2014 20:15:08 GMT

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

Re: Calculation between columns

Wed, 12 Feb 2014 20:21:43 GMT

I read your mind and was just doing that 🙂

Re: Re: Calculation between columns

Wed, 12 Feb 2014 21:06:08 GMT

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

Re: Calculation between columns

Wed, 12 Feb 2014 21:25:25 GMT

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

Wed, 12 Feb 2014 23:44:09 GMT

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

Re: Calculation between columns

Thu, 13 Feb 2014 19:19:10 GMT

Dear Dathu,

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

Best regards,

Dorine