Create Master Calendar for End of Month reporting date

[SR2]

Hello Experts,

I want to create End of Month reporting date field for last 12 months so that I can use it as a dimension in my QlikSense reports.

E.g. monthend(today()) = 31-03-2021

I want to create a field say EOMDate as follows -

31-03-2021
28-02-2021
31-01-2021
.
.
31-03-2020

I use couple of dates in measures expression so I could not use any of those to create reporting date dimension as it will not give correct measure values.

My line of thought is  - Create a date variable in Data load script and iterate it in a loop to populate a table with EOMDate as a field and use it as a dimension.

Can you help/show how this can be achieved (if it can be) in Data load script?

Many thanks,

SR

[salonicdk28 · Accepted solution]

Please try this once-

Let varMinDate = Num(Makedate(Year(Today())-1,Month(Today()),Day(Today())));
Let varMaxDate = Num(Makedate(Year(today()),Month(Today()),Day(Today())));

Datefield:
LOAD MonthEnd(date($(varMinDate)+IterNo()-1)) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

Calender:
LOAD
Datefield AS Date,
Year(Datefield) AS Year,
Month(Datefield) as Month,
Day(Datefield) AS Day,
'Q ' & Num(Ceil(Month(Datefield)/3),'(ROM)0') AS Quarter

RESIDENT Datefield;

DROP TABLE Datefield;

[SR2]

Thank you so much. This is exactly what I needed.

I have one question on writing expressions.

Currently, I have my measures written as below in a Table (for all 12 months separately). I want to use Date dimension created using your solution and use it to create measure. How can I achieve following  using set analysis ?

COUNT(
{<
[Invoice Hold Count] -= {0}
,[Payment Method] = {'Direct Debit'}
[Invoice Hold Date] = {"<=$(=MonthEnd(AddMonths(today(),-12)))"}
[Invoice Release Date] = {">$(=MonthEnd(AddMonths(today(),-12)))"}
>}
[Invoice Number])

If I simply replace the date logic in expression above with Date field then it gives me null result. 

Thanks,