topic Re: Ratio Calculation in QlikView

Ratio Calculation

upaliwije — Mon, 13 Oct 2014 08:24:24 GMT

Hi Friends

DESCRIPTION	ACTUAL 2014	Actual Ratio	ACTUAL 2013	Actual Ratio
SALES	7,332,885	100	967,954	100
LABOUR	339,527	5	222,262	5
MATERIAL	831,338	11	338,969	11
COMMISSION	890,125	12	75,000	12
GROSS_PROFIT	5,271,896	72	331,722	72
STAFF_EXP	853,494	12	40,000	12
ADMIN_EXP	857,451	12	80,000	12
SELLING_EXP	509,639	7	100,000	7
TOTAL_EXPENSES	2,220,584	30	220,000	30
OTH_INCOME	40,000	1	40,000	1
NET_PROFIT	3,091,312	42	151,722	42

I have calculated Actual Ratio bases on the Sales Value of Each Year for 2014 and 2013.But The calculation returns the same ratios for both years . Kindly have look at my QV document and help me to correct the expression to suit my requirement

Labour/Sales *100 339527/7332885*100=5%

Marerial/Sales*100 831338/7332885*100=11%

Re: Ratio Calculation

ariel_klien — Mon, 13 Oct 2014 08:33:49 GMT

change the variable to:

vActual = sum({$<DESCRIPTION={'SALES'},YEAR=>}ACTUAL)

vBudget = sum({$<DESCRIPTION={'SALES'},YEAR=>}BUDGET)

Ariel

Re: Ratio Calculation

Anonymous — Mon, 13 Oct 2014 08:37:14 GMT

I can't see the the similarity between values for both years.........in your application,

The values are different for different years..........

Whats your issue...

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 08:43:16 GMT

Values are different but the Ratio is same for both years

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 08:44:19 GMT

Thanks

It is not working. Can apply it to my QV file and upload the files

Re: Re: Ratio Calculation

ariel_klien — Mon, 13 Oct 2014 08:46:09 GMT

I attached the file

Re: Ratio Calculation

Anonymous — Mon, 13 Oct 2014 08:46:27 GMT

EVEN RATIO IS ALSO DIFFERENT ..............

Please see

Re: Ratio Calculation

jagan — Mon, 13 Oct 2014 08:48:12 GMT

Hi,

Put = in your variable declaration, then use this expression

(Sum(ACTUAL)/(vActual))*100

where vActual ::::: = sum({$<DESCRIPTION={'SALES'},YEAR=>}ACTUAL)

Putting = will evaluate the expression and stored values in variable.

Regards,

Jagan.

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 08:50:32 GMT

Pls have a look at Years Sheet

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 09:00:06 GMT

It wont give the desired results

DESCRIPTION	ACTUAL 2014	Actual Ratio	ACTUAL 2013	Actual Ratio
SALES	7,332,885	88	967,954	88
LABOUR	339,527	4	222,262	4
MATERIAL	831,338	10	338,969	10
COMMISSION	890,125	11	75,000	11
GROSS_PROFIT	5,271,896	64	331,722	64
STAFF_EXP	853,494	10	40,000	10
ADMIN_EXP	857,451	10	80,000	10
SELLING_EXP	509,639	6	100,000	6
TOTAL_EXPENSES	2,220,584	27	220,000	27
OTH_INCOME	40,000	0	40,000	0
NET_PROFIT	3,091,312	37	151,722	37

Sales Ratio should be 100% (7332885/7332885)*100

and Ratio for both years are same

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 10:03:16 GMT

Pls help me with an answer

Re: Ratio Calculation

ariel_klien — Mon, 13 Oct 2014 10:06:13 GMT

change the variable to:

vActual = sum(if(DESCRIPTION='SALES',ACTUAL))

vBudget = sum(if(DESCRIPTION='SALES',BUDGET))

Ariel

Re: Ratio Calculation

jagan — Mon, 13 Oct 2014 10:09:39 GMT

Hi,

Did you tried my earlier reply.

Regards,

Jagan.

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 10:15:16 GMT

Tried But It won't Work.

Will you please further look into this and help me

Re: Ratio Calculation

ariel_klien — Mon, 13 Oct 2014 10:20:49 GMT

try to change the variable with 'IF' like in my earlier replay

Re: Re: Ratio Calculation

Anonymous — Mon, 13 Oct 2014 10:33:09 GMT

PFA

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 10:38:29 GMT

Still not working please

Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 11:32:04 GMT

Thanks Gupta

Your expression is working. You have created variable for each year. Is there a possibility of creating one variable and specifying the year through the expression. If possible Pls let me know

Re: Ratio Calculation

jyothish8807 — Mon, 13 Oct 2014 12:03:02 GMT

Hi Upali,

PFA.

This automated as you want.It is depended on Single variable.

When you select 2013 the date will chage for 2013 and when you select 2014 the numbers will change.

Hope it may help.

Regards

Re: Re: Ratio Calculation

upaliwije — Mon, 13 Oct 2014 15:41:46 GMT

Thanks Kc

I am almost there with your QV document. But there is a little problem with the Ratio Calculation for the year 2013. Because I want Figures to appear in respect of several Years in different columns. Pls check and advise me