Spead income by dates in month

[gidon500]

Hi guys

I need help , enclosed is an excel file

the input is

invoice , amount , fromdate to date

I need to spead the amount by months , while take in consideration

number of days in a month , which calculated as 30 days per month

thanks for your help

gidon

[sunny_talwar · Accepted solution]

I am sure there mught be a better way to do this, but I was able to go this far:

Table:

LOAD Invoice,

  Amount,

  FromDate,

  ToDate,

  Date(AddMonths(MonthEnd(FromDate), (IterNo() -1)), 'MMM-YYYY') as Date,

  If(IterNo() = 1, 30 - Day(FromDate),

  If(Floor(AddMonths(MonthEnd(FromDate), (IterNo() -1))) = Floor(MonthEnd(ToDate)),

  If(Match(Num(Month(ToDate)), 1, 3, 5, 7, 8, 10, 12) and Match(Day(ToDate), 31) or

  Match(Num(Month(ToDate)), 2) and Day(ToDate)>= 28, 30, Day(ToDate)), 30)) as Days

While AddMonths(MonthEnd(FromDate), (IterNo() -1)) <= MonthEnd(ToDate);

LOAD * Inline [

Invoice, Amount, FromDate, ToDate

100, 1500, 10/11/2015, 6/2/2016

101, 1000, 1/12/2015, 31/01/2016

102, 2700, 27/12/2015, 2/2/2016

];

Join(Table)

LOAD Invoice,

  Sum(Days) as Sum

Resident Table

Group By Invoice;

FinalTable:

LOAD Invoice,

  Date,

  (Amount * Days)/Sum as AmountPerMonth

Resident Table;

DROP Table Table;

The complication I faced was around getting 30 days for each month instead of the actual number of days. Somebody else might have a better solution

[maxgro]

V:

LOAD

  *,

  (daysfirstmonth/30 + monthdiff + daylastmonth/30) as period;

LOAD

  *,

  year(todate)*12 + month(todate) - (Year(fromdate)*12 + Month(fromdate))-1 as monthdiff,

  rangemin(30 - day(fromdate), 30)  as daysfirstmonth,

  rangemin(day(todate) - 1 +1,30)  as daylastmonth

  ;

LOAD

  //MonthStart(todate) as end,

  //Date(floor(MonthEnd(fromdate))+1) as start, 

  Invoice,

    amount,

    fromdate,

    todate

FROM

[Spead Income.xlsx]

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(Invoice))>0

;

Left Join (V)

load

  fromdate,

  date(AddMonths(MonthStart(fromdate), IterNo()-1), 'MMM-YY') as month

Resident V

While AddMonths(MonthStart(fromdate), IterNo()-1) <= MonthStart(todate);

Left Join (V) LOAD

  fromdate, month, 1 as IsFirst

Resident V

Where Peek('fromdate') <> fromdate

order by fromdate, month;

Left Join (V) LOAD  fromdate, month, 1 as IsLast

Resident V

Where Peek('fromdate') <> fromdate

order by fromdate, month desc;

[gidon500]

Hello

thank you very much

gidon

[gidon500]

Hi

thanks for your help

gidon