Multiply across Rows

[carlcimino]

Trying to multiply the Rate Change where Data Type = RATE to the Statutory Written Premium where data type = STAT by Year.  The Dollar Change results below the data are what I get in excel.  Any help would be appreciated.

Year	Rate Change	Statutory Written Premium	Data Type
2010	-0.013	$0	RATE
2012	0.056	$0	RATE
2013	0.086	$0	RATE
2014	0	$0	RATE
2014	0.04	$0	RATE
2015	-0.008	$0	RATE
2016	0.03	$0	RATE
2009	-	$112,404,000	STAT
2010	-	$114,932,000	STAT
2011	-	$119,944,000	STAT
2012	-	$127,579,000	STAT
2013	-	$135,964,000	STAT
2014	-	$143,446,000	STAT
2015	-	$147,480,000	STAT
2016	-	$148,946,797	STAT
		Dollar Change
2009		-
2010		(1,494,116)
2011		6,716,864
2012		10,971,794
2013		-
2014		5,737,840
2015		(1,179,840)
2016		4,468,404

[eduardo_dimperio]

Hi,

You could do this in your script

AUX:

LOAD

Rate Change,

Data Type

RESIDENT YOURTABLE

where  Data Type = RATE;

LEFT JOIN (AUX)

LOAD

Statutory Written Premium,

Data Type

RESIDENT YOURTABLE

where  Data Type = STAT;

FINAL:

LOAD

*,

Rate Change *Statutory Written Premium AS Dollar Change

RESIDENT AUX

[eduardo_dimperio]

Maybe

Set Analysis:

//Aggr({SetExpression}[DISTINCT] [NODISTINCT ] expr, StructuredParameter{, StructuredParameter})

AGGR(

SUM({<Data Type={RATE}>}Rate Change)

*

SUM({<Data Type={STAT}>} Statutory Written Premium)

,YEAR)

[carlcimino]

This worked as expected.  Is there any performance hit by doing it via the aggr function versus doing it in the script with resident loads and joins?