Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
thank you
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);