Create a Master Calendar to derive Month and YTD as column

[QVUser1]

Hi Folks ,

I need to create a column in master Calendar  looks like :

Jan-20

Feb-20

Mar-20

Apr-20

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

YTD

I have a single Expression  and this desired column is used to drag top on the pivot table .

[sk88024]

Hi,

You can create the master calendar using the below script. I have attached the Samples_Date file in file attachment. The below script will give you MonthYear column and current YearToDate (YTD). 

Also, a pivot table can created using MonthYear as Dimension and YTD as an expression. 

MaterCalendar:
LOAD
DATE(Date,'DD/MM/YYYY') AS Date
FROM
[..\QV Sample Data\Samples_Date.xlsx]
(ooxml, embedded labels, table is Sheet1);

Range:
Load
min(Date) as minDate,
max(Date) as maxDate
Resident MaterCalendar;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Range;

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:
Load
TempDate AS Date,
Month(TempDate)& '-' & Right(Year(TempDate), 2) As MonthYear,
YeartoDate(TempDate)*-1 as YTD //Current Year Flag
Resident TempCalendar Order By TempDate ASC;

Drop Table TempCalendar;

[QVUser1]

Hi,

I want to get month and YTD in same column

[Brett_Bleess]

Best I can offer is the following specific Design Blog post:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

If you want to check out the other posts in that area you can use the following link to search further:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett