4 Replies Latest reply: Oct 9, 2016 12:52 PM by john cena RSS

    Set Analysis Current Month vs Previous Month

      Hi

      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

      =sum({<Style=>}Charge)

       

      My Master Calendar is

      MasterCalendar:

      LOAD

        TempDate AS LocalTime,

        Date(Floor(TempDate)) as LocalDay,

        Hour(TempDate) AS LocalHour,

        GasDay,

        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

            =SUM({<Year={'$(vPriorMonthYear)'},Month={'$(vPriorMonth)'}>}Charge)

             

            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:

               

              =sum({$<

              Style=

              ,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

                 

                Table:

                LOAD * INLINE [

                  Country, MonthYear, Value

                  Argentina,Jun2016,550

                  Japan,Jun2016,200

                  America,Jun2016,100

                  America,May2016,300

                  Argentina,May2016,250

                  Japan,May2016,150

                  America,Apr2016,200

                  Argentina,Apr2016,170

                  Japan,Apr2016,210

                ];

                 

                My output is below