Split amount in range date

[kprotrka]

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.

[mfchmielowski]

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.

[kprotrka]

it is not working...you have inbox message...thanks