2 Replies Latest reply: Mar 31, 2016 5:33 AM by Sunny Talwar

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

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.

• ###### Re: Tag Previous Month, Last Month and Last Year in Master Calendar

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)

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

• ###### Re: Tag Previous Month, Last Month and Last Year in Master Calendar

Check this out:

Script:

QuartersMap:

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:

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