topic Re: Bifurcating -monthly in QlikView

Bifurcating -monthly

varunjain — Tue, 01 Oct 2013 19:13:38 GMT

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.

Re: Bifurcating -monthly

Tue, 01 Oct 2013 20:20:09 GMT

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)

......

Re: Bifurcating -monthly

Tue, 01 Oct 2013 22:25:19 GMT

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;