1 Reply Latest reply: Jun 16, 2015 5:13 AM by Xavier Retaillaud RSS

    Expression Formula for This Month and Last Month

      Hi Guys,

       

      I'm new to Qlik Sense. Anyone knows the Expression Formula for 'current month' vs. 'last month'? I have an example table below.

           

      ChannelSessionsMonthYearMonthYear
      Paid Search837May2014May 2014
      Organic Search860May2014May 2014
      Organic Search728June2014June 2014
      Paid Search805June2014June 2014
      Organic Search173July2014July 2014
      Paid Search266July2014July 2014
      Organic Search114April2015April 2015
      Paid Search728April2015April 2015
      Organic Search111May2015May 2015
      Paid Search583May2015May 2015

       

      From the table, for example, I want to get the sessions for May 2015 vs. April 2015 for each channel in a table format. Also, to get the sessions for May 2015 vs. May 2014. So the output would be.

       

                                       Sessions

      ChannelMay 2015April 2015
      Organic Search
      Paid Search

       

                                      Sessions

      ChannelMay 2015May 2014
      Organic Search
      Paid Search

       

      Thanks a lot for your help.

        • Re: Expression Formula for This Month and Last Month
          Xavier Retaillaud

          Hi,

           

          For each output you have to use a pivot table with

          Line : Channel

          Measures : 2 set analysis expressions (1 for each column)

           

          To make the set analysis easier i suggest to add a field "MonthNumber" in your table. You can make this with an expression or with a mapping table in the script.

           

          Current Year - Current Month vs Previous Month

          1st measure should look like : Min({<MonthNumber={$(=month(today())+0)},Year={$(=year(today()))}>} Sessions)

          2nd : Min({<MonthNumber={$(=month(today())-1)},Year={$(=year(today()))}>} Sessions)

           

          Current Year-Month vs Previous Year-Month

          1st measure should look like : Min({<MonthNumber={$(=month(today())+0)},Year={$(=year(today()))}>} Sessions)

          2nd : Min({<MonthNumber={$(=month(today())+0)},Year={$(=year(today())-1)}>} Sessions)

           

          NB : the +0 is important for convert text to number.