Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
On which Date Jan 16 is calculated?
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
So what will be the output for your current sample?
Hi Kush,
Please find the screenshot below,
Thanks,
Sasi
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))
Thanks a lot kush. It is working.
that's gr8
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
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