Discussion Board for collaboration related to QlikView App Development.
hello, it seems to work correctly but I need to split by day and not by month (the referencedate must be on a daily basis).
Hi, are you sure of the daily basis? in example a cost from 31/03 to 30/04, maybe it's a 2-month payment is done at end of the month and it will be 50/50 between months, if it's on daily basis march will get only 1/31 and april the others 30/31.
Yes. I need to split the costs on a daily basis and group them by month / quarter. therefore if a cost goes from 30/03 to 15/05, the costs will be split for 1 day in March, 30 in April and 15 in May.
thanks a lot
Probably it will need some debug but can be something like:
data: LOAD *, expected*(daysMonth/totalDays) as expectedByMonth_DayBase ; LOAD *, //start and finish in the same month If(MonthName(schedfinish)=MonthName(schedstart) ,schedfinish-schedstart //count of the first month ,If(MonthName(referenceDate)=MonthName(schedstart) ,Floor(MonthEnd(schedstart))-schedstart+1 // count of the last month ,If(MonthName(referenceDate)=MonthName(schedfinish) ,schedfinish-MonthStart(schedfinish)+1 // full months ,Floor(MonthEnd(referenceDate))-MonthStart(referenceDate)+1 ))) as daysMonth, schedfinish-schedstart as totalDays ; LOAD *, expected/numMonths as expectedByMonth, addMonths(schedstart,IterNo()-1) as referenceDate While numMonths>=IterNo(); LOAD wo, expected, final, type, schedstart, schedfinish, ((Year(schedfinish)*12)+Month(schedfinish)) - ((Year(schedstart)*12)+Month(schedstart)) + 1 as numMonths FROM [.\cost.xlsx] (ooxml, embedded labels, table is cost);