Linking YearMonth from Data table to Master Calendar

[Anonymous]

Hi All,

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:

MinMaxDate:

Load

Peek('YearMonth',0,'Employee Details') as MinDate,

Date('31/12/2020','YYYYMM') as MaxDate

AutoGenerate 1;

Let vMinDate = NUM(PEEK('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxDate'));

Calendar:

Load

Month(TempDate) as Month,

Date(TempDate) as Date,

Year(TempDate) & Num(Month(TempDate), '00') as YearMonth,

Year(TempDate) as Year;

Load

  $(vMinDate) + IterNo() - 1 as TempDate

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

[sunny_talwar · Accepted solution]

How is YearMonth created in your main table? Is it possible that it is an actual date vs the yearmonth in your calendar is a text -> Year(TempDate) & Num(Month(TempDate), '00') as YearMonth,

[Anonymous]

Hi Sunny,

During the extract phase YearMonth is created as a date field I think:

Date(AddMonths(Today(),-1),'YYYYMM') as YearMonth,

Ignore the addmonth bit

Does this mean in the calendar I need to make YearMonth as a date, if so do you know the syntax to do so that will match it?

Thanks

[Anonymous]

Hi Sunny,

I have now solved the issue:

I put Date(YearMonth, 'YYYYMM') as YearMonth in the main table and it now works.

Thank you for triggering me to check that