6 Replies Latest reply: May 24, 2018 5:26 AM by Zak Pullen RSS

    Set Analysis Current Month vs Previous Month


      I'm having a little bit of trouble getting the result I need having had a look at some similar examples.  I am trying to produce a table that shows the aggregate charges for the current month, and then show what the previous month was.  I have some list boxes with years and months in them to enable me to change which month I'm looking at.


      In my table, Current Month is just



      My Master Calendar is



        TempDate AS LocalTime,

        Date(Floor(TempDate)) as LocalDay,

        Hour(TempDate) AS LocalHour,


        IF(Hour(TempDate)<=6,Hour(TempDate)+18, Hour(TempDate)-6) AS GasHour,

        Week(GasDay) AS Week,

        Year(GasDay) AS Year,

        Month(GasDay) AS Month,

        num(year(GasDay))*100+num(Month(GasDay)) As year_month_num,

        Day(GasDay) AS Day,

        Weekday(GasDay) AS WeekDay,

        DayNumberOfYear(GasDay) As DayofYear,

        Day(GasDay) & ' ' & Month(GasDay) as DayMonth,

        'Q' & ceil(month(GasDay) / 3) AS Quarter,

        Date(monthstart(GasDay), 'MMM-YYYY') AS MonthYear,

        Week(GasDay)&'-'&Year(GasDay) AS WeekYear,

        inyeartodate(GasDay, $(vToday), 0) * -1 AS CurYTDFlag,

        inyeartodate(GasDay, $(vToday), -1) * -1 AS LastYTDFlag

      RESIDENT TempCal2

      ORDER BY TempDate ASC;


      I added the year_month_num as other examples seemed to indicate this was a good way of doing the month comparison.


      For my Previous Month, I thought this would work, but it just returns the same result (I'm not even sure why it does that)

      =sum({$<Style=, year_month_num={"=$(=year_month_num-1)"}>}Charge)


      Also, I dont think the year_month_num will work when we select January, as this would be 201501 for instance, and 1 less than that is 201500 not 201412.


      Can anyone help?

        • Re: Set Analysis Current Month vs Previous Month
          Gysbert Wassenaar

          You could use this to create a number field that you can use to calculate the previous month:

          num(year(GasDay))*12+num(Month(GasDay)) As year_month_num


          Or try MonthStart(GasDay) as GasMonth and use as expression =sum({$<Style=,GasMonth={"=$(MonthStart(max(GasMonth),-1))"}>}Charge)

          • Re: Set Analysis Current Month vs Previous Month

            I did some more searching after I posted this and have determined a solution.  I used this page as a reference and adapted to suit my scenario

            QlikView App: Set Analysis - Prior Period Comparison


            First I added to my load script the following

            let vPriorMonth  = '=month(addmonths(max(GasDay),-1))';

            let vPriorMonthYear = '=Year(addmonths(max(GasDay),-1))';


            Then I used this for finding the last month charges



            I'm sure I could have done it without using the variables but that is the bit that trips me up most easily so as long as it works that suits me.

            • Re: Set Analysis Current Month vs Previous Month
              Miguel Braga

              Hi Jamie,


              Take note that when you do this you need to consider the year as well so you basically need to something like this:




              ,Year = {$(=Year(addmonths(max(GasDay),-1)))},

              ,Month= {$(=(num(month(addmonths(max(date($(=Max(Year)) & '-' & num($(=max(num(Month))),'00') & '-' & '01', 'YYYY-MM-DD')),-1)))))}

                >} Charge)


              Hope this solution provide you what you need

              • Re: Set Analysis Current Month vs Previous Month
                john cena

                Hi Jamie Coombs, i'm struggling with this part since the last 1 week


                My Requirement is actually this if a user selects the current month the higest 2 values in current month

                and i want to see how these higest values are in previous 3 months user can select any month,
                if he selects any month the previous 3 months data should appear. please help me out



                LOAD * INLINE [

                  Country, MonthYear, Value












                My output is below

                • Re: Set Analysis Current Month vs Previous Month
                  Vatsal Ajmera


                  The easiest solution to this would be to the monthAgo calculation in the Qlik Calender.

                  create an auto calendar for the date field you are trying to visualize by using the Derive fields function in the calendar script. (  I can attach the complete auto calendar script if needed)


                  ex:-  DERIVE FIELDS FROM FIELDS [date Field]


                  now, for current month the code in the measure field would be :

                  Sum( {$ < [Date.autoCalendar.MonthsAgo] = {'0'}>} sales)

                  U can change the 0 to 1,2,3 to go back to n last months.


                  Hope this helps.