Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two dates(Start and End Date).
If today is greater then Start date than i am taking today() instead of Start Date.
I need to split my budget between those two dates(Start and End Date) per month.
I have to split it based on this calculation(field):
round(num(End_Date-date(today()))/50) as Faktor
That means it should be splitted in following order:
Today+Faktor as First_Month
Today+Faktor+Faktor as Second_Month
Problem is that End_Date could be 01.01.2050
I would like to get it in one column all months and years and not to create hundreds of columns for each month.
Then i need to divide the budget with the numbers of the months between those 2 dates.
Final result should be shown in the table.
Hi.
The sample code i've writen:
sub initCalendar
Dates:
LOAD
min(dateStart) as minDate
, max(dateEnd) as maxDate
RESIDENT
BudgetWithStartDates
;
let varMinDate = Num(peek('minDate',0, 'Dates'));
Let varMaxDate = Num(peek('maxDate',0, 'Dates'));
drop table Dates;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate)
;
MasterCalendar:
Load Distinct
TempDate
, year(TempDate) &'-'& num(month(TempDate), '00') as tempDateYearMonth
, Floor(MonthEnd(TempDate)) - Floor(MonthStart(TempDate)) + 1 as tempDateDaysInMonth
Resident
TempCalendar
Order By
TempDate ASC
;
Drop Table TempCalendar;
end sub;
sub matchIntervals
IntervalMatchTable:
IntervalMatch (TempDate)
Load
distinct
dateStart
, dateEnd
Resident
BudgetWithStartDates
;
NoConcatenate
Bridge:
load
TempDate
, dateStart &'|'& dateEnd as datesKey
Resident
IntervalMatchTable
;
drop table IntervalMatchTable;
Left join(Bridge)
load * Resident MasterCalendar;
drop table MasterCalendar;
NoConcatenate
BridgeTmp:
load Distinct datesKey, tempDateYearMonth Resident Bridge;
drop table Bridge;
NoConcatenate
Bridge:
load datesKey, tempDateYearMonth, if(Previous(datesKey)=datesKey, offsetBase - (year(dateStart)*12 + month(dateStart)), 0) as offset;
load datesKey, tempDateYearMonth, left(tempDateYearMonth,4)*12 + Right(tempDateYearMonth,2) as offsetBase, SubField(datesKey,'|',1) as dateStart Resident BridgeTmp order by datesKey, tempDateYearMonth;
drop table BridgeTmp;
left join(BudgetWithStartDates)
load * Resident Bridge;
drop table Bridge;
end sub;
sub main
BudgetWithStartDates:
load *
, dateStart &'|'& dateEnd as datesKey
, round(num(dateEnd-date(today()))/50) as Faktor
, (year(dateEnd)*12 + month(dateEnd)) - (year(dateStart)*12 + month(dateStart)) as numberOfBudgetMonths
inline [
product, dateStart, dateEnd, budgetMonthlyValue
A, 2021-05-01, 2022-05-01, 100
B, 2021-06-01, 2022-09-01, 10
];
call initCalendar;
call matchIntervals;
drop fields datesKey;
end sub;
sub main;
You dont have to have multiple columns. Your counting rule is Today + (Factor * offset).
What is and how to count offset? This is tricky one 😉
What is offset: difference in months between startDate and budgetMonth.
How to count it: for every pair of start/end dates prepare the calendar table with list of month between start/end date. Then use the IntervalMatch function and join the results.
> "Then i need to divide the budget with the numbers of the months between those 2 dates."
(year(dateEnd)*12 + month(dateEnd)) - (year(dateStart)*12 + month(dateStart)) as numberOfMonths
This is the easiest way (in my opinion) to count number of months between two dates.
it is not working...you have inbox message...thanks