Bifurcating -monthly

[varunjain]

Need your help again guys!!!

We have a record with Start Date , End date and AMOUNT for Billing. Now, if the billing frequency is Yearly, then the record would be like Start date = 1-Jan-2012 , End date = 31-Dec-2012 and Amount lets say 12000. We want instead of 1 record, there should be 12 different records on a momnthly basis as in one record for 1 Jan-2012 to 31-Jan-2012 and amount 1000 then next record 1-Feb-2012 to 28-Feb-2012 and amount 1000 and so on... Think this could be done through Loop but dont know how.. Please help.

[#2]

For this requirmnet, you need to create different buckets for date(Dimension) like below.

Jan=IF(Date>='01/01/2013' and Date<='01/31/2013',Date)
Feb=IF(Date>='02/01/2013' and Date<='02/28/2013',Date)

......

[#3]

Hi Varun,

You could do this for simple differences that don't bridge years.

raw_data:

LOAD * INLINE [

    Start date, End date, Amount

    01/01/2012, 31/12/2012, 12000

];

//Get month numbers

Let rowStartMonthNo = num(Date(Month(peek('Start date',0,'raw_data')),'MM'));

Let rowEndMonthNo = num(Date(Month(peek('End date',0,'raw_data')),'MM'));

//Now build new table

for i=$(rowStartMonthNo) to $(rowEndMonthNo)

    output:

    LOAD

        AddMonths([Start date],$(i)-1) as newStartDate,

        MonthEnd([Start date],$(i)-1) as newEndDate,

        Amount/(1+$(rowEndMonthNo)-$(rowStartMonthNo)) as newAmount

    Resident raw_data;

Next

drop table raw_data;