3 Replies Latest reply on Apr 4, 2016 6:29 AM by Alex Welch

    Trouble with Month over Month Metrics

    Alex Welch

      Hi All,

       

      I am running into an issue where I need to compare the value of a variable  (account value in this example) to the value of the same variable just as of the last day of the previous month.  Below are my three (3) equations. Separately they work. [Selected Date] is a parameter and returns the correct value no matter what date I select. The [value last month] is also returning correctly. It is when I use equation #3 that it returns blank. I have no idea way and can't seem to get it to work.

       

      1) -------------------------------

      // Value Last Month

      if [date_value] = [Last day of previous month] then

          [account_value]

      end

       

      2) -------------------------------

      // Value Today

      if [date_value]= [Selected Date] then [account_value] end

       

      3) -------------------------------

      [Value Today] - [Value Last Month]

        • 1. Re: Trouble with Month over Month Metrics
          Łukasz Majewski

          You are using row level calculation. [date_value] cannot be equal to [Selected Date] and [Last day of previous month] at the same time - if the latter two are different dates -  just as #2016-04-03# may be equal to today but is not  equal to #2016-03-03#. The blank you are getting is a result of NULL returned when your condition is not met as you did not use 'ELSE' expression.

          Please search this forums for possible answers on how to calculate this (this kind of question comes up every day here) or attach (using advanced editor) your packaged workbook (.twbx).

           

          edit: not sure what you are doing but your formula may return what you expect if you use aggregates like this:

          SUM([Value Today]) - SUM([Value Last Month])
          
          1 of 1 people found this helpful
          • 2. Re: Trouble with Month over Month Metrics
            Manideep Bhattacharyya

            Output.JPG

            Create three Field and One Parameter.

             

            Parameter Name: Date Selector (Date Type)

             

            Calculated Field 1: Value Today// If User Select this as "1st April 2014" then this will return sales value for that day.

            if [Order Date] = [Date Selector] then [Sales] END

             

            Calculated Field 2: Value Last Month // This will return sales value for 31st March 2014

            if [Order Date] = DATETRUNC('month',DATEADD('month',0,[Date Selector]))-1 then [Sales] END

             

            Calculated Field 3: Differece Value

            sum([Value Today]) -sum([Value Last Month])

             

            Then arrange the Workbook in the above mentioned way.

            • 3. Re: Trouble with Month over Month Metrics
              Alex Welch

              Ended up using MAX instead of SUM. But the next level of aggregation is what fixed it