I am having an issue linking YearMonth from my Data Table to my Master Calendar.
I have data for each month from 201701 to 201712, however I would like calendar to go from 201701 to 202012.
I have got this far. You'll see that for the months where I have data, it has repeated the months, one with the data matching and one with 0. Does anyone know how I can prevent this so there is a simple link that shows a flow of dates from 2017 to 2020 where data aligns to its according date.
Here is my calendar script:
Peek('YearMonth',0,'Employee Details') as MinDate,
Date('31/12/2020','YYYYMM') as MaxDate
Let vMinDate = NUM(PEEK('MinDate',0,'MinMaxDate'));
Let vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxDate'));
Month(TempDate) as Month,
Date(TempDate) as Date,
Year(TempDate) & Num(Month(TempDate), '00') as YearMonth,
Year(TempDate) as Year;
$(vMinDate) + IterNo() - 1 as TempDate
While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);