Splitting the Premium monthwise in the Straight Table

[#1]

Dear QVExpert,

Premium Amount  for one year, which is of $26.

We have Warranty Start Date & Warranty End Date. Based on the period, the Earned Premium is calculated.

For two months, then (26 /365) * 60, the calculation is correct.

Now can we have monthwise in individual rows.

For example, Jan16, (26/365) *31, 2.2082191

                    Feb16, (26/365)*29, 2.06575342

Can you please suggest me.

Thanks,

Sasi

[Kushal_Chawda]

On which Date Jan 16 is calculated?

[#3]

Hi Kushal,

The Premium Amount is for whole year.

But we need to calculate for two months.

From 01-Jan-2016 To 29-Feb-2016.

But we need to show in two rows as Jan-16 -  2.2082191

                                                      Feb-16 - 2.0657532

Suppose, if it is between 05-Jan-2016 to 16-Feb-2016 Then

Jan-16,  (26/365) *27, 1.9232876

Feb-16,   (26/365) *16,  1.1397260

Thanks Again

[Kushal_Chawda]

So what will be the output for your current sample?

[#5]

Hi Kush,

Please find the screenshot below,

Thanks,

Sasi

[Kushal_Chawda · Accepted solution]

Try this

Data:

LOAD *,

         MonthName(Date) as MonthYear;

LOAD

Certificate_No,

Start_Date,

End_Date,

Date(Start_Date+iterno()-1) as Date

Premium_Amt

FROM table

while Start_Date+iterno()-1 <= End_Date;

Create the Straight Table

Dimension:

MonthYear

Certificate_No

Expression:

= (sum(distinct Premium_Amt)/365) * Count(distinct Date)

or below expression

= sum(aggr(sum(distinct Premium_Amt),Certificate_No,MonthYear))/365 * sum(aggr(Count(distinct Date),Certificate_No,MonthYear))

[#7]

Thanks a lot kush. It is working.

[Kushal_Chawda]

that's gr8

[#9]

Hi Kush,

Another scenario,

Start_Date & End_Date are from tables. It is working fine now.

How it is handled, if they control the dates from Calendar objects.

For Example,

In Calendar Object, i select 01/Feb/2016 to 31/Mar/2016

$(vFiscalStartDate) is '02/01/2016'

$(vFiscalEndDate) is '03/31/2016'

Please suggest

Thanks

Sasi

[Kushal_Chawda]

First you create the Date like below

Data:

LOAD

Certificate_No,

Start_Date,

End_Date,

Date(Start_Date+iterno()-1) as Date

Premium_Amt

FROM table

while Start_Date+iterno()-1 <= End_Date;

Now create the Calendar on this Date and link this Date with Calendar Date

Update : You can create MonthYear in Calendar