Multiply Row-wise Duration data with Sectoral Weighted Average

[Rich5678]

Hello,

I have data as follows:

In below table, Sector, Sec ID, MV and Duration are provided in Data Table  (see below inline script).

MV weight to be calculated at Sector Level. For the first row, MV Weight is determined as 1,500 / (1,500+2,200+1,400) = 0.294. In other words, when I add the MV weights of all Industrial Sec IDs, I get 1 (0.294 + 0.431 + 0.275)

MV_Wt * Duration to be calculated as MV_Wt * Duration, and aggregated at Sector Level. Ultimately, the interest is only to view result at Sector Level (not at Sec_ID level) - please see desired output at the bottom of this question.

Sector	Sec ID	MV	MV_Wt	Duration	MV_Wt * Duration
Industrial	abc123	1500	0.294	9	2.647
Industrial	def456	2200	0.431	3	1.294
Industrial	ghi789	1400	0.275	7	1.922
Banking	bcd234	3200	0.360	6.5	2.337
Banking	efg567	4100	0.461	5.4	2.488
Banking	hij892	1600	0.180	8	1.438
Mining	xyz123	5500	0.470	9.2	4.325
Mining	yzx456	6200	0.530	6.8	3.603

Modifying the question, to add Inline Script for Data.

For MV_Wt column calculation, I tried below and other permutations, but they don't work

aggr(MV / sum(MV),Sector)  

To make my question easier, below is the loading script with values

Temp:
LOAD * inline [
Sector|Sec_ID|MV|Duration
Industrial|abc123|1500|9
Industrial|def456|2200|3
Industrial|ghi789|1400|7
Banking|bcd234|3200|6.5
Banking|efg567|4100|5.4
Banking|hij892|1600|8
Mining|xyz123|5500|9.2
Mining|yzx456|6200|6.8
] (delimiter is '|');

Could someone please help me, get 

MV_Wt = aggregate [ MV / Sum(MV), at Sector Level]

Wtd_Duration = aggregate ( MV_Wt * Duration, at Sector Level)

In other words, resulting output should be as below:

Sector	MV	Wtd_ Duration
Industrial	5100	5.863
Banking	8900	6.263
Mining	11700	7.928

Thanks

[vidyutverma]

Its very easy to show this in your second table. You don`t even need to use the Total keyword. Secret is to simplify your formula. If Sector is your dimension, then to get the weighted duration, the denominator would be just sum(MV), and the Numerator would be ? Try this and see if you get the desired result.

[Rich5678]

Hi, thanks for your quick reply..

For MV_Wt column calculation, I tried below and other permutations, but they don't work

aggr(MV / sum(MV),Sector)  

To make my question easier, below is the loading script with values

Temp:
LOAD * inline [
Sector|Sec_ID|MV|Duration
Industrial|abc123|1500|9
Industrial|def456|2200|3
Industrial|ghi789|1400|7
Banking|bcd234|3200|6.5
Banking|efg567|4100|5.4
Banking|hij892|1600|8
Mining|xyz123|5500|9.2
Mining|yzx456|6200|6.8
] (delimiter is '|');

Could someone please help me, get 

MV_Wt = aggregate [ MV / Sum(MV), at Sector Level]

Wtd_Duration = aggregate ( MV_Wt * Duration, at Sector Level)

In other words, resulting output should be as below:

Sector	MV	Wtd_ Duration
Industrial	5100	5.863
Banking	8900	6.263
Mining	11700	7.928

Thanks

[MarcoWedel · Accepted solution]

one solution might be:

Sum(MV*Duration)/Sum(MV)

hope this helps
Marco

[Rich5678]

Many thanks Marco,

Works perfectly.  Have a great weekend 🙂