EXPRESSION MODIFICATION

[#1]

Dear Experts,

I have a pivot table which looks like below,

BCOD	MO_NM	CLA_CODE	EFF_MONTH	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
B100	MOTOR	M3		148916	158661	164360	158743	158506	140203	186970	202715	155078	178589
		M4		37313	-8892	31127	22245	38612	31066	35182	91030	71030	21120
		MC		2117618	2175753	1913049	1858664	1975620	1798944	2124056	2254747	2474338	2893513
		Total		2303847	2325522	2108536	2039653	2172738	1970213	2346209	2548491	2700447	3093222
	NON_MOTOR	EN		116400	-66000	34315	58800	30000	301550	34000	32095	26650	79580
		FR		145313	24567	119970	84460	-6379	145310	20684	25670	240172	8142
		HH		801	35125	32875	92325	23690	-105220	46977	-7215	-17034	10641
		MS		48652	160564	65028	156041	218236	178873	219810	178535	-21717	-18022
		PP		43439	38965	34565	17289	21726	26677	37848	43033	29476	32494
		SS		52700	28200	3700	-28200	28200	56400	-84600	47960	29960	40460
		TC		46761	145345	189729	134618	189818	49486	279052	166747	230740	255439
		TT		3300	-	-	-	-	-	-	-	6250	43450
		Total		457366	366766	480182	515333	505291	653076	553772	486825	524497	452183

In this presentation only expression is SUM(AMOUNT_2016)

My problem is how can  i modify above expression to achieve percentages from the total  instead of above figures. then the total must be 100.

Rgds,

Priyantha.

[sushil353]

Hi,

Try this expression:

SUM(AMOUNT_2016)/SUM(Total<CLA_CODE>AMOUNT_2016)

HTH

Sushil

[Lech_Miszkiewicz]

It is important also to determine dimensionality - so the question is whether your percentage should be calculate from subtotal on CLA_CODE or maybe MO_NM level or on any other level. Then ultimetly change value in those brackets

<CLA_CODE> as per Sushil's post

regards

Lech

[#4]

Dear Sushi,

I modified with your assistance and got the following expression.

ROUND(SUM(AMOUNT_2016)/SUM(Total<MO_NM>AMOUNT_2016),0.0001)

But expected output does not achieved.

Look at the resulted output.

BCOD	MO_NM	CLA_CODE	EFF_MONTH	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
B100	MOTOR	M3		0.0063	0.0067	0.0070	0.0067	0.0067	0.0059	0.0079	0.0086	0.0066	0.0076
		M4		0.0016	-0.0004	0.0013	0.0009	0.0016	0.0013	0.0015	0.0039	0.0030	0.0009
		MC		0.0897	0.0922	0.0810	0.0787	0.0837	0.0762	0.0900	0.0955	0.1048	0.1226
		Total		0.0976	0.0985	0.0893	0.0864	0.0920	0.0835	0.0994	0.1079	0.1144	0.1310
	NON_MOTOR	EN		0.0233	-0.0132	0.0069	0.0118	0.0060	0.0604	0.0068	0.0064	0.0053	0.0159
		FR		0.0291	0.0049	0.0240	0.0169	-0.0013	0.0291	0.0041	0.0051	0.0481	0.0016
		HH		0.0002	0.0070	0.0066	0.0185	0.0047	-0.0211	0.0094	-0.0014	-0.0034	0.0021
		MS		0.0097	0.0321	0.0130	0.0312	0.0437	0.0358	0.0440	0.0357	-0.0043	-0.0036
		PP		0.0087	0.0078	0.0069	0.0035	0.0043	0.0053	0.0076	0.0086	0.0059	0.0065
		SS		0.0105	0.0056	0.0007	-0.0056	0.0056	0.0113	-0.0169	0.0096	0.0060	0.0081
		TC		0.0094	0.0291	0.0380	0.0269	0.0380	0.0099	0.0559	0.0334	0.0462	0.0511
		TT		0.0007	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0000	0.0013	0.0087
		Total		0.0916	0.0734	0.0961	0.1032	0.1012	0.1307	0.1109	0.0975	0.1050	0.0905

with this calculation total raw must have the 1.0000 as the answer in both Motor and Non_Motor

Can you please find the mistake i have done here.

[Lech_Miszkiewicz]

Hi,

From what i am counting the total is 1 for both - so there is no mistake, unless you want total for each month as well, dont you?

This would be then different granularity <CLA_CODE>

hth

Lech

[chhavi376]

Hi Priyantha,

Please have a look at the attached QV.

[Lech_Miszkiewicz]

Hi,

I quess this is what you wanted:

=sum(AMOUNT)/SUM(Total<MO_NM, EFF_MONTH>AMOUNT)

thath gives you this result:

If you need different calculation, maybe provide and XLS example what you're trying to achieve.

kind regards

Lech