Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Gabri11
Contributor
Contributor

Split amount range date

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
 
1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

5 Replies
rubenmarin

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.
Gabri11
Contributor
Contributor
Author

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

rubenmarin

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.

Gabri11
Contributor
Contributor
Author

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

rubenmarin

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