Skip to content

Instantly share code, notes, and snippets.

@ralfbecher

ralfbecher/QlikView_Calc_Days_per_Month_of_TimeSpans.qvs

Created June 13, 2014 13:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ralfbecher/caef30a7f468d211d3d1 to your computer and use it in GitHub Desktop.
Save ralfbecher/caef30a7f468d211d3d1 to your computer and use it in GitHub Desktop.
Download ZIP
Calculate days per month from time span events in QlikView
Raw
QlikView_Calc_Days_per_Month_of_TimeSpans.qvs
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