3 Replies Latest reply on Feb 20, 2018 5:12 AM by Jim Dehner

    Calculations on Previous month data

    Prajval T
    Prajval T Feb 19, 2018 12:03 AM

      Hi Team,

       

      I have view like below, where i need to show Recent 6 months sales, Goal, Rate and other measures based on the month and year selection.

      Below are the calculations.

       

      Goal = [Rate]*([Sales])

       

      Rate  should be of previous month's.

       

      for example Goal for August = July Rate * (sales)

                                                    = 61*63121 (should be like around 3850381).

       

      Can any one help me how to get the previous month data for every month and calculate Goal?

       

      And also  i need show QTD, PQTD and QTD Variance in the same view.

       

       

       

      Can any one please help me how to do achieve this requirement?

       

      Regards,

      Amala

      • 227 Views
      • Tags:
        • 1. Re: Calculations on Previous month data
          Jim Dehner
          Jim Dehner Ambassador Feb 19, 2018 8:51 AM (in response to Prajval T)

          Good morning

          please see the attached

           

          I have 2 solutions for you - one using your original formulas and a second that I modified

          first using yours

           

          your goal formula becomes

           

          and it returns this

           

           

           

          but I have a question regarding your rate calculation -

          usually in calculating rates the measures are aggregated - see below

           

           

          then the goal formula becomes

           

           

          now that is going to produce very different results

           

           

          you know your data and analysis - just looked a little different - you can go either way

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Calculations on Previous month data
            Prajval T
            Prajval T Feb 19, 2018 8:50 PM (in response to Jim Dehner)

            Thank you JIM for the reply.

             

            I already tried with look up. it will work fine but it will not give any value for first month on the window.

             

            In the above example, it is showing empty. But i need to show the value for July also.

             

             

            And About Rate calculations, i just gave you an example with sample data. But i am doing summation in actual data.

             

            Can you please help me how to get the data for July month also with same logic(Previous Month Rate * sum(Sales)).

             

            Regards,

            Prajwal

            • 3. Re: Calculations on Previous month data
              Jim Dehner
              Jim Dehner Ambassador Feb 20, 2018 5:12 AM (in response to Prajval T)

              see the attached

              the reason that july did not show up is that you had a filter to limit the data to 6 month - Tableau needed the preceding month to make the calculation

              (don't worry I'll show you how to get the first month out of the viz)

               

               

              Start by changing the the formula for the filter as shown below

              add the filter to Context (right click the filter and select add to context)

               

               

              That will get you the viz below with june showing - to hide the first month (it will adjust automatically as you update the viz)

              right click the month and select hid - see below

               

               

               

              you end up with this

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.