Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
Hello
thank you very much
gidon
Hi
thanks for your help
gidon