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

    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).

      Master Calendar.JPG


      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
          bruno bertels



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




          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 ...



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

            Check this out:






            MAPPING LOAD 

            rowno() as Month, 

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

            AutoGenerate (12); 


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

            Let varMaxDate = Num(Today()); 



            LOAD $(varMinDate) + Iterno()-1 As Num, 

                Date($(varMinDate) + IterNo() - 1) as TempDate 

            AutoGenerate 1

            While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 



            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