New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

Split amount in range date

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.

2 Replies
Creator II

Hi.

The sample code i've writen:

``````sub initCalendar
Dates:
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:
\$(varMinDate) + Iterno()-1 As Num,
Date(\$(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While \$(varMinDate) + IterNo() -1 <= \$(varMaxDate)
;

MasterCalendar:
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)
distinct
dateStart
, dateEnd
Resident
BudgetWithStartDates
;

NoConcatenate
Bridge:
TempDate
, dateStart &'|'& dateEnd as datesKey
Resident
IntervalMatchTable
;

drop table IntervalMatchTable;

Left join(Bridge)

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)

drop table Bridge;
end sub;

sub main

BudgetWithStartDates:
, 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.

Contributor III
Author

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

Tags
Community Browser