4 Replies Latest reply: Feb 23, 2016 12:23 AM by gidon david

# Spead  income by dates in month

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

gidon

• ###### Re: Spead  income by dates in month

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

Table:

Amount,

FromDate,

ToDate,

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

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

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

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)

Sum(Days) as Sum

Resident Table

Group By Invoice;

FinalTable:

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

• ###### Re: Spead  income by dates in month

Hello

thank you very much

gidon

• ###### Re: Spead  income by dates in month

V:

*,

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

*,

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

;

//MonthStart(todate) as end,

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

Invoice,

amount,

fromdate,

todate

FROM

(ooxml, embedded labels, table is Sheet1)

Where Len(Trim(Invoice))>0

;

Left Join (V)

fromdate,

Resident V

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;

• ###### Re: Spead  income by dates in month

Hi