topic Re: Ratio issue in QlikView

Ratio issue

upaliwije — Sun, 17 Jul 2016 05:54:07 GMT

I have following Pivot table in my documnet

SITUATION 1
DESCRIPTION	ACTUAL	ACT_RATIO
INCOME	2,665,244,812	100
EXPENSES	256,525,524	10

SITUATION 2
LOCATION	DEPT 1		DEPT 2		DEPT 3
DESCRIPTION	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO
INCOME	1,278,071,014	48	866,770,079	33	520,403,719	20
EXPENSES	108,549,183	4	72,843,841	3	75,132,500	3

Under situation 1 when I divide the expenses from income i get the correct ratio of 10%

but under situation 2 when I do the same after adding Dept dimension I get a incorrect ratio

Ratio result I am expecting should as give below

SITUATION 2
LOCATION	DEPT 1		DEPT 2		DEPT 3
DESCRIPTION	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO
INCONME	1,278,071,014	100	866,770,079	100	520,403,719	100
EXPENSES	108,549,183	8	72,843,841	8	75,132,500	14

My expression is =fabs((Sum(ACTUAL)/$(=$(=vActual))*100))

pls help me get correct results from Oivot table

Re: Ratio issue

MK_QSL — Sun, 17 Jul 2016 08:02:00 GMT

Use

SUM(ACTUAL)/SUM(TOTAL <LOCATION> ACTUAL)

Re: Ratio issue

upaliwije — Sun, 17 Jul 2016 14:32:40 GMT

Tks

How can I modify my expression (=fabs((Sum(ACTUAL)/$(=$(=vActual))*100)))with location

Re: Ratio issue

MK_QSL — Sun, 17 Jul 2016 14:50:36 GMT

What is the expression for vActual?

Re: Ratio issue

upaliwije — Sun, 17 Jul 2016 15:08:14 GMT

I have created a variable for income as follows

vActual=sum({$<DESCRIPTION={'INCOME'}>}ACTUAL)

Re: Ratio issue

MK_QSL — Sun, 17 Jul 2016 15:10:29 GMT

change it to

SUM(TOTAL <LOCATION> {<DESCRIPTION = {'INCOME'}>}ACTUAL)

Re: Ratio issue

upaliwije — Sun, 17 Jul 2016 15:21:52 GMT

I have done it still the result is like this

LOCATION	DEPT 1		DEPT 2		DEPT 3
DESCRIPTION	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO
INCOME	1,278,071,014	48	866,770,079	33	520,403,719	20
EXPENSES	108,549,183	4	72,843,841	3	75,132,500	3

Re: Ratio issue

MK_QSL — Sun, 17 Jul 2016 15:25:18 GMT

Provide sample app

Re: Ratio issue

upaliwije — Sun, 17 Jul 2016 15:54:08 GMT

I m attaching a sampal file pls

Re: Ratio issue

MK_QSL — Sun, 17 Jul 2016 16:00:58 GMT

Can you provide the expected result in excel file?

This file having different figure than original question

Re: Ratio issue

upaliwije — Sun, 17 Jul 2016 16:06:08 GMT

LOCATION	CITY	CITY	COLOMBO_&_SUBURBS	COLOMBO_&_SUBURBS	OTHER_CITIES	OTHER_CITIES
DESCRIPTION	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO	ACTUAL	ACT_RATIO
GROSS_PREMIUM	164,678,927	100	34,421,366	100	102,631,879	100
SRCC_PREMIUM	11,812,857	7	1,748,528	5	4,990,016	5
FOREIGN_PREMIUM	80,082,876	49	7,380,467	21	16,143,075	16
CEDED_PREMIUM	91,895,732	56	9,128,995	27	21,133,090	21

Re: Ratio issue

upaliwije — Sun, 17 Jul 2016 16:37:33 GMT

Dear Manish

Pls help me

Re: Ratio issue

MK_QSL — Mon, 18 Jul 2016 07:59:28 GMT

Sorry after your last reply, I was outside and not having computer..

Use below expression...

=fabs((Sum(ACTUAL)/SUM(TOTAL <LOCATION> {<DESCRIPTION = {'GROSS_PREMIUM'}>}ACTUAL)*100))

Re: Ratio issue

upaliwije — Mon, 18 Jul 2016 08:18:26 GMT

Thanks Manish for your help