Create Month Periods

[#1]

Hi,

I need to create a chart which shows how many claims have been incurred within a Policy Month. i.e. Policy Inception Month = Policy Month 1 regardless of the actual month name. I am struggling to define this in my script. Ideally I also need to show the information by Policy Week and Policy Day too. Any ideas would be greatly appreciated.

So far I have defined the following in my script, but this does help when setting the months up as a chart dimension:

Month(PolicyInceptionDate) as PolicyMonth1,

Month(Addmonths(PolicyInceptionDate,1)) as PolicyMonth2,

Month(Addmonths(PolicyInceptionDate, 2)) as PolicyMonth3,

Month(Addmonths(PolicyInceptionDate, 3)) as PolicyMonth4,

Month(Addmonths(PolicyInceptionDate, 4)) as PolicyMonth5,

Month(Addmonths(PolicyInceptionDate, 5)) as PolicyMonth6,

Month(Addmonths(PolicyInceptionDate, 6)) as PolicyMonth7,

Month(Addmonths(PolicyInceptionDate, 7)) as PolicyMonth8,

Month(Addmonths(PolicyInceptionDate, 8)) as PolicyMonth9,

Month(Addmonths(PolicyInceptionDate, 9)) as PolicyMonth10,

Month(Addmonths(PolicyInceptionDate, 10)) as PolicyMonth11,

Month(Addmonths(PolicyInceptionDate, 11)) as PolicyMonth12,

Many thanks,

Jayne

[Frank_Hartmann]

Try to connect your table to master calendar

[#3]

Thanks Frank. My master calendar does not look at this month however and I am still unsure as to how I would code this?

[Frank_Hartmann]

can you provide a qvw for testing?

[Peter_Cammaert]

Well, I guess that this is not as simple as it sounds. At least not for months. You are trying to calculate three numbers for each Claim: the PolicyMonthNumber, the PolicyWeekNumber and the PolicyDayNumber.

For weeks and days, the expression is simple:

Days: ClaimDate - PolicyInceptionDate + 1

Weeks: (ClaimDate - PolicyInceptionDate)/7 + 1

For months, there is this problem of varying month lengths... No clue yet.

Peter

[#6]

Hi Peter,

Thank you for the response. It's more the PolicyInceptionDate that I need to identify the dates for. So for example if a policy was incepted in March, I would like March to become equal to one, April to become 2, May as 3 etc and I would need this to be the case for each policy, regardless of when it starts. So that in a chart, I could have the numbers 1 to 12 along the x-axis as Policy Month and then the expression would look at which month of the policy the claim occurred to see whether on average the claims frequency is higher at a certain point of a policy year.

I hope that this makes sense.

Jayne

[Peter_Cammaert]

Months:

1 + (Month(ClaimDate) + Year(ClaimDate))*12) - (Month(PolicyInceptionDate) + Year(PolicyInceptionDate)*12) +

IF ((Month(ClaimDate) + Year(ClaimDate))*12) - (Month(PolicyInceptionDate) + Year(PolicyInceptionDate)*12) > 0,

IF (Day(ClaimDate) >= Day(PolicyInceptionDate), 1, 0), 0)

Not very proud of this one though.

Peter..

[Peter_Cammaert]

That's exactly what I'm trying to accomplish. But you don't need to tag PolicyInceptionDate to know in what PolicyMonth the Claim fits... You only need to calculate the PolicyMonth of the ClaimDate versus the corresponding PolicyInceptionDate. See my example below.

[#9]

Thanks Peter, I appreciate the help

[Peter_Cammaert]

This document does some of the things we discussed above.

It seems to work pretty well.

Peter