Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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)
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?