Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys
i need help , i got this table of income :
invoice | fromdate | todate | sum |
150 | 15/01/2015 | 22/03/2015 | 300 |
151 | 04/02/2015 | 17/06/2015 | 600 |
152 | 17/03/2015 | 13/07/2015 | 1200 |
as you can see invoice 150 is from jan 15 to march 22
the total income for this period is 300
i need to spread the income for jan as 15 days , feb total month , and march 22 days
so each period get a portion from the 300
Invoices 151 and 152 are two more examples .
I got tousands of them during a year
thanks for the help .
gidon
something like this
Table:
LOAD * Inline [
invoice, fromdate, todate, sum
150, 15/01/2015, 22/03/2015, 300
151, 04/02/2015, 17/06/2015, 600
152, 17/03/2015, 13/07/2015, 1200
];
Data:
LOAD *,
date(monthstart(fromdate)+(iterNo()-1),'DD-MM-YYYY') as Date,
monthname(monthstart(fromdate)+(iterNo()-1)) as MonthName,
sum/(todate - monthstart(fromdate)+1) as DaySum
RESIDENT Table
WHILE iterNo() <= (todate - monthstart(fromdate)) + 1;
DROP Table Table;
Try the following script:
Table:
LOAD *,
sum/Diff as EachDaySum;
LOAD *,
todate - fromdate + 1 as Diff;
LOAD * Inline [
invoice, fromdate, todate, sum
150, 15/01/2015, 22/03/2015, 300
151, 04/02/2015, 17/06/2015, 600
152, 17/03/2015, 13/07/2015, 1200
];
Temp:
LOAD Min(fromdate) as minDate,
Max(todate) as maxDate
Resident Table;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1
While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Join (Table)
IntervalMatch (Date)
LOAD fromdate,
todate
Resident Table;
something like this
Table:
LOAD * Inline [
invoice, fromdate, todate, sum
150, 15/01/2015, 22/03/2015, 300
151, 04/02/2015, 17/06/2015, 600
152, 17/03/2015, 13/07/2015, 1200
];
Data:
LOAD *,
date(monthstart(fromdate)+(iterNo()-1),'DD-MM-YYYY') as Date,
monthname(monthstart(fromdate)+(iterNo()-1)) as MonthName,
sum/(todate - monthstart(fromdate)+1) as DaySum
RESIDENT Table
WHILE iterNo() <= (todate - monthstart(fromdate)) + 1;
DROP Table Table;
Hello
thanks it works great
gidon
Hello
thanks it works
gidon
I am glad that I was able to help
Awesome
Glad it did.
Best,
Sunny
hi
i found a problem
if fromdate is 15/01/2015
it consider like date is 01/01/2015
can you look at it
thanks
gidon
Hi ,
That is because, I have used monthstart function over fromdate.
try below
Table:
LOAD * Inline [
invoice, fromdate, todate, sum
150, 15/01/2015, 22/03/2015, 300
151, 04/02/2015, 17/06/2015, 600
152, 17/03/2015, 13/07/2015, 1200
];
Data:
LOAD *,
date(fromdate+(iterNo()-1),'DD-MM-YYYY') as Date,
monthname(fromdate+(iterNo()-1)) as MonthName,
sum/(todate - fromdate+1) as DaySum
RESIDENT Table
WHILE iterNo() <= todate -fromdate + 1;
DROP Table Table;
hi
thanks , now it works perfect
gidon