Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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.

View solution in original post

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