Master Calendar with Year, (Monthly FromDate) and (MonthlyTo Dates) Auto generated

[Peter_Brunner]

Hi Guys ,

I have been trying to replicate an ERP system Master Calendar in Qlikview with not much luck.

I have the basic calendar but need to add From the 1st of every Month and ToEnd of every Month as per attached image.  

So in the case of July the from date is  01/07/2019  and To date is 31/07/2019

appeciate any help on this

Regards Peter

Let StartYear = 2022;
Let EndYear = 2023;

Master_Calendar:
Load
Date(MonthStart(InvDates))AS StartDate,
Date(MonthEnd(InvDates)) AS EndDate,
MonthName(InvDates) as MonthName,
QuarterName(InvDates) as QuarterName,
YearName(InvDates) as YearName,
'Q'&Ceil(Month(InvDates)/3) as Quarter,
Year(InvDates) as Year,
Month(InvDates) as Month,
WeekDay(InvDates) as WeekDay,
Week(InvDates) as Week,
Day(InvDates) as Day,
Year(InvDates) as yearDate
;

[BrunPierre]

 MonthStart(InvDates) as CalPeriod_FromDate,
 MonthEnd(InvDates) as CalPeriod_ToDate

[marcus_sommer · Accepted solution]

If you have already a start- and an end-date you could just apply a loop to generate the dates between them - in your case maybe with something like:

t:
load *, year(Date) * 12 + month(Date) as RunningPeriod; // example for whatever you may need
load *, date(startdate + iterno() - 1) as Date
from YourSource while startdate + iterno() - 1 <= enddate;

[Peter_Brunner]

Thanks Marcus