3 Replies Latest reply on Nov 26, 2018 11:44 AM by Jennifer VonHagel

    Dynamic last 3 months sum for multiple product

    Aabhas Aeran
    Aabhas Aeran Nov 26, 2018 8:50 AM

      Hi,

      I am tracking the sales of each of my products in last 3 months via Tableau.But I am stuck at doing so. Would appreciate some help in the caculation.

      Logic - For Aug, the output will be sum of sales in June, July and August. Similarly, for Sep it will be sum of July , Aug and Sep. This is only a sample data, actual data spans across 3 years.

      Input file :

       

      Output File:

      Thanks.

      • 51 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: Dynamic last 3 months sum for multiple product
          Norbert Maijoor
          Norbert Maijoor Ambassador Nov 26, 2018 9:30 AM (in response to Aabhas Aeran)

          Hi Aabhas,

           

          Find my approach as reference below and stored in attached workbook version 2018.2 located in the original thread

           

           

          display.png

           

          Hope it helps,

           

          Regards,

          Norbert

          • 2. Re: Dynamic last 3 months sum for multiple product
            Aabhas Aeran
            Aabhas Aeran Nov 26, 2018 10:01 AM (in response to Norbert Maijoor)

            Thanks, but it gives the sum from today till last 3 months from today. However, my requirement is that in Aug, it gives sum for last 3 months including August.

             

            So for current data, Last 3 months sum for P1 in Aug will be 93+54+74+84 i.e sum of june data +sum of july data +sum of august data

            • 3. Re: Dynamic last 3 months sum for multiple product
              Jennifer VonHagel
              Jennifer VonHagel Nov 26, 2018 11:44 AM (in response to Aabhas Aeran)

              Hi Aabhas, I think a Table calculation might help.  This will compute a rolling 3 month sum.

               

               

              This calculation can do different things in the view depending on how you set it after it is in the view.  Be sure that it is set to look at columns across the table - which in this setup is months (rather than looking up/down the table - SubCategory).  Click the arrow on the Measure pill, choose Compute Using > and choose Table Across. For this option, be sure your months are sorted in order so that the previous two columns are the correct ones to Sum with the current column.

               

              The Rolling 3 Months calculation needs the months in its calculation to actually be in the view. So for instance, if you're find rolling three months for June 2018, April and May have to be in the view - you can't filter them out or they won't be counted in June's sum.  What you can do so that you're only seeing the month or months you care about is create a calculation like Norbert's, put it in the view, and then hide the columns that you don't want to see.

               

              Here you can see an example calculation - this will find the last 3 months not counting the current month, but you can make this calculation anything you like.

               

              Put it on the Columns shelf after Month(Date). If you put it before Month(Date), the Month(Date) sort order will be off.  You can see this returns True or False depending on whether it's the Months you want to show.  Right-click on any False value, and choose Hide.

               

              Now you only have the three months you want.  You can get rid of the "True" column header. Right-click on any True value, and un-check "Show Header"

               

               

              Best,

              Jennifer