topic Re: Multiply across Rows in App Development

Multiply across Rows

carlcimino — Wed, 23 May 2018 18:38:29 GMT

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

Re: Multiply across Rows

eduardo_dimperio — Wed, 23 May 2018 19:02:56 GMT

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

Re: Multiply across Rows

eduardo_dimperio — Wed, 23 May 2018 19:10:23 GMT

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)

Re: Multiply across Rows

carlcimino — Wed, 23 May 2018 19:31:17 GMT

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?