topic Split amount range date in QlikView

Split amount range date

Gabri11 — Fri, 03 May 2019 20:20:30 GMT

Hi everyone, I have a problem with the split of costs (expected and end) in the relative range of dates (schedstart and schedfinish).
My goal is to split the costs on a daily basis and then aggregate them by day, month, semester and year by grouping them by type.

my goal is the green box in the attached excel file

Thank's

Re: Split amount range date

rubenmarin — Sun, 05 May 2019 10:56:05 GMT

Hi,l assuming that all months gets the same amount it can be something like:
data:
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);

From referenceDate you can get year and month, and expectedByMonth will be the amount of that month.

Re: Split amount range date

Gabri11 — Mon, 06 May 2019 06:58:26 GMT

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

Re: Split amount range date

rubenmarin — Tue, 07 May 2019 08:10:31 GMT

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.

Re: Split amount range date

Gabri11 — Tue, 07 May 2019 10:47:48 GMT

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

Re: Split amount range date

rubenmarin — Tue, 07 May 2019 16:18:58 GMT

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);