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

    May Javier

      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

          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

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

            Check this out:

             

            Capture.PNG

             

            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