topic % computation in QlikView

% computation

tracycrown — Sun, 16 May 2010 05:24:33 GMT

Dear all

Can someone help to enlightern me on how to compute the % in yellow below:

For example: Travel / School Fees = 25,000/500,000 = 5%

Desired Output

Desc	Jan-10	Jan-10
	Actual	%

School Fees	500,000	100.0%

Travel	25,000	5.0%
Stationery	50,000	10.0%
Salaries	350,000	70.0%
Total	425,000	85.0%

Thanks for your quick response, please send me a example QV file. Attached is an excel input file.

Tracy

% computation

Sun, 16 May 2010 07:47:36 GMT

See this file: SchoolComputing.qvw

Do u wants like this?

Thanks

Zaman

% computation

tracycrown — Sun, 16 May 2010 10:14:46 GMT

Dear Zaman

This is exactly what I want but how come there is nothing in the script, please advise.

Thank you so much, Tracy

% computation

Sun, 16 May 2010 10:40:22 GMT

Hi Tracy

Please see this Qlikview documents for script and expressions.

Don't give reload on QlikView 10. You can use Qlikview 8.5 or 9

Thanks

Zaman

% computation

tracycrown — Mon, 17 May 2010 18:20:26 GMT

Dear Zaman

Thanks for your quick response but I did not receive any email at all.

I don't understand the script below, please advise what does it mean and how do it ?.

[..\Example-1.xls]

(biff, embedded labels, table is Sheet1$, filters(Remove

(Row, Pos(Top, 3)),

Remove

(Row, Pos(Top, 1))));

I also notice that you had changed 'School Fees' to SchoolFees (without " " and space), is this the way to change a string to a memory variable ?.

If(Desc='School Fees',Sum (ActualValue)/SchoolFees,If(Desc='Travel',Sum (ActualValue)/SchoolFees,If(Desc='Stationery',Sum (ActualValue)/SchoolFees,If(Desc='Salaries',Sum (ActualValue)/SchoolFees,If(Desc='Total',Sum (ActualValue)/SchoolFees)))))

In addition, kindly advise how to create a total column in QV (which is not in Excel) showing sum of all months from Jan to Dec and %.

Thank you very very much, Tracy

% computation

Tue, 18 May 2010 06:33:50 GMT

Hi..

SchoolFees is a variable which Value is: = Sum(if(Desc='School Fees',ActualValue))

see on the Documents Properties then Variable

and this is the expression to view your output

thanks

zaman

% computation

Tue, 18 May 2010 06:38:20 GMT

Hi..

Qus : In addition, kindly advise how to create a total column in QV (which is not in Excel) showing sum of all months from Jan to Dec and %.

Ans: Can you please show me your output on excel? and then send me please.

thanks

zaman

% computation

Tue, 18 May 2010 07:16:17 GMT

Hi,

Check this attached application.

Hope this will help you.

- Peterson.

% computation

tracycrown — Sat, 22 May 2010 00:39:31 GMT

Dear Kevin

Thanks for your quick response, please advise why and how to do the following :

Remove

(Row, Pos(Top, 1

)),

Remove

(Row, Pos(Top, 2

)),

Transpose

(),

Remove

(Col, Pos(Top, 6))

Thank you so much for your help, Tracy

% computation

tracycrown — Sat, 22 May 2010 00:54:25 GMT

Hi Zaman

Sorry, I was away for few days and unable to answer your response.

First of all, thank you so much for your explanation on memory variable, I understand how to do it now. This is an important topic and not sure why this is not on QV tutorial ?.

For the Total Column, I hope QV can do it dynamically depending on user selection. For example, if user highlights from Jan to Mar, then QV will sum all cost items from Jan to Mar in Total column and compute the %.

Thank you very much for your help, hope to hear from you soon. Tracy

% computation

Sat, 22 May 2010 09:09:32 GMT

Hi Tracy

Do u wants like this? See this attachment.

Thanks

Zaman

% computation

tracycrown — Sun, 23 May 2010 02:50:43 GMT

Dear Zaman

At least this method do not require any creation of memory variable (same as Kevin's idea). I am still don't understand how to generate the following scripts, can you describe the steps :

FROM

(

biff, embedded labels, table is Sheet1$, filters

(Remove (Row, Pos(Top, 1)),

Remove

(Row, Pos(Top, 2)),

Transpose

(),

Remove

(Col, Pos(Top, 6))));

Thank you very much for your help, Tracy

% computation

tracycrown — Mon, 31 May 2010 01:53:32 GMT

Dear all

Can someone help to explain why and how to do the following in script :

(Remove (Row, Pos(Top, 1)), Remove

(Row, Pos(Top, 2)),

Transpose

(),

Remove

Thank you very much, Tracy

(Col, Pos(Top, 6))));