I'm trying to allocate premium to a calendar quarter based on the number of days that an insurance policy was effective during that calendar quarter. Insurance policies normally run for 12 months from the effective date to the expiry date. So, if a policy becomes effective on the 15th of April, I want to calculate that I should allocate the number of days between April 15th and June 30th / 365 * Premium to the 2nd quarter.
The thing I'm missing is the calendar dates. I don't have them in my data set so I don't know how to make a calculated field reference those dates. Can you help?
Here's the start of a formula that I was working on. It's a bit more complicated because our policies might be cancelled before 12 months and our system doesn't overwrite the Expiration date with the new Termination date:
datediff('day',min(DATE(End of Quarter),DATE([Expiration]),DATE([Termination])),max(DATE(Start of Quarter),DATE([Effective])))/365 * sum([Written_Premium])
Would you be able to post a sample workbook (twbx file)?