Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Calculate days per month from time span events in QlikView
Data:
LOAD * INLINE [
id, start_date, end_date
1234, 01.01.2014, 20.04.2014
5678, 23.02.2014, 25.03.2014
9012, 30.03.2014, 01.04.2014
];
Result:
NOCONCATENATE LOAD
id,
MonthName(start_date, IterNo()-1) as month,
If(IterNo()=1,
Floor(MonthEnd(start_date)) - start_date,
If(MonthStart(AddMonths(start_date, IterNo()-1)) = MonthStart(end_date),
end_date - MonthStart(end_date),
Floor(MonthEnd(AddMonths(start_date, IterNo()-1))) - MonthStart(AddMonths(start_date, IterNo()-1)))) +1 as count_days
Resident Data
While MonthStart(AddMonths(start_date, IterNo()-1)) <= MonthStart(end_date);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.