[SOLVED] 12 Month Rolling Earned Premium

I am trying to create a trending chart that displays two year's worth of days of 12 month cumulative premium. I would have one data point for the premiums earned from today's date back 365 days (ignoring leap year) and another point from yesterday's date back 365 days, then two days ago and so on...

Would it be best to use MacGyver to create all of the days using a giant table or should I try calculating one of the endpoint's annual total then when I go to process the next day, I increment the prior day's total by the one end point's premium amount and decrementing that by the other end point's premium amount?

Both ways sound like a lot of processing.

Thanks for your advice.
John

This message has been edited. Last edited by: <Kathryn Henning>,

WebFOCUS 8008
Windows, All Outputs

Posts: 14 | Registered: April 10, 2014

MartinY

Virtuoso

posted

Hide Post

Yes, should be a lot of processing because you'll have to read your data 365 times to include proper period of time to create your premium total per day...

Here little code that can be used to calculate the from/to dates for the WHERE clause:

-SET &DATE = &YYMD;
-SET &I = 0;
-LOOP
-SET &I = &I + 1;
-SET &STDATE = AYMD(&DATE,    -&I, 'I8');
-SET &ENDATE = AYMD(&STDATE, -365, 'I8');
-TYPE &STDATE, &ENDATE

-SET &PREM&I = 0;

TABLE FILE X
SUM PREMIUM
WHERE DATE GT &ENDATE AND DATE LE &STDATE;
ON TABLE HOLD AS RESULT FORMAT ALPHA
END
-READFILE RESULT
-SET &PREM&I - &PREMIUM;
-IF &I LT 365 THEN GOTO LOOP;

You then have one variable per day with the premium amount

This message has been edited. Last edited by: MartinY,

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

RSquared

Guru

posted

Hide Post

John,

Are you doing an Earned Premium Triangle or just a linear report?

WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit