topic Re: Create Month Periods in QlikView

Create Month Periods

Mon, 01 Dec 2014 15:25:57 GMT

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

Re: Create Month Periods

Frank_Hartmann — Mon, 01 Dec 2014 15:35:46 GMT

Try to connect your table to master calendar

Re: Create Month Periods

Mon, 01 Dec 2014 15:41:15 GMT

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

Re: Create Month Periods

Frank_Hartmann — Mon, 01 Dec 2014 15:48:01 GMT

can you provide a qvw for testing?

Re: Create Month Periods

Peter_Cammaert — Mon, 01 Dec 2014 15:57:07 GMT

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

Re: Create Month Periods

Mon, 01 Dec 2014 16:09:24 GMT

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

Re: Create Month Periods

Peter_Cammaert — Mon, 01 Dec 2014 16:18:43 GMT

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..

Re: Create Month Periods

Peter_Cammaert — Mon, 01 Dec 2014 16:24:46 GMT

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.

Re: Create Month Periods

Mon, 01 Dec 2014 16:35:51 GMT

Thanks Peter, I appreciate the help

Re: Re: Create Month Periods

Peter_Cammaert — Mon, 01 Dec 2014 17:01:30 GMT

This document does some of the things we discussed above.

It seems to work pretty well.

Peter

Re: Create Month Periods

Mon, 01 Dec 2014 17:04:53 GMT

Thanks Peter, I will take a look