Tag Previous Month, Last Month and Last Year in Master Calendar

[#1]

Hello! Is it possible to flag a particular date dimension (e.g last month, previous month and last year) in the Master Calendar?

I would imagine the data result to be like the image below -- a single column that contains all the tags, the tags are rolling according to the current month (Say, today is April 2016, Last Month would be March 2016, Previous Month would be February 2016 and Last Year would be March 2015, all else would be NULL).

This is gonna be useful for me as I want to create a table (in the sheet) that's gonna treat these tags as a dimension against various measures (or KPIs).

Cheers and thanks in advance to all those who peeked and will give answers/ideas! You are all appreciated.

[brunobertels]

Hi

You will find a lot of ressource for master calendar here :

https://community.qlik.com/docs/DOC-8843

then for your date flag may be somethink like this : ( assuming "date" is the name of your date field)

Load

if(year(date)=year(today()-1,'Last Year',

if(month(date)=month(today()-1),'Last Month',

if(month(date)=month(today()-2),'Previous Month'))) as "Flag Calendar",

From ...

regards

[sunny_talwar]

Check this out:

Script:

QuartersMap: 

MAPPING LOAD 

rowno() as Month, 

'Q' & Ceil (RowNo()/3) as Quarter 

AutoGenerate (12); 

Let varMinDate = Num(MakeDate(2012, 12, 31)); 

Let varMaxDate = Num(Today()); 

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, 

    Week(TempDate) as Week, 

    Year(TempDate) as Year, 

    Month(TempDate) as Month, 

    Day(TempDate) as Day, 

    YearToDate(TempDate)*-1 as CurYTDFlag, 

    YearToDate(TempDate,-1)*-1 as LastYTDFlag, 

    InYear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

    Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear, 

    ApplyMap('QuartersMap', Month(TempDate), Null()) as Quarter, 

    Week(WeekStart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

    WeekDay(TempDate) as WeekDay,

  If(MonthName(Today()) = MonthName(TempDate), 'This Month',

    If(MonthName(AddMonths(Today(), -1)) = MonthName(TempDate), 'Last Month',

    If(MonthName(AddYears(Today(), -1)) = MonthName(TempDate), 'Last Year'))) as Flag

Resident TempCalendar 

Order By TempDate; 

DROP Table TempCalendar;

UPDATE: I did this in QlikView, but the script will remain the same in Qlik Sense