3 Replies Latest reply on Mar 15, 2013 7:34 AM by sira.sasitorn

    Question about Moving Calculation

    sira.sasitorn

      Hi,

       

      I'm trying to count the number of items in each month going back 12 months. So for example:

       

      For the month of Nov 2012, the value will be Nov 2012 + values from the past 12 months (Nov 11+ Dev 11 + Jan 12 + Feb 12 + ....+ Oct 12).

       

      One way that I can think of right now is using "Moving Calculation" and set the previous value to 12. This works perfectly, however,  if I have a quick filter on the month and filtered out some months, the value will change depending on how many records it has above (since the previous value of 12 is based on the previous 12 records.).

       

      Is there a way that I can have the logic above and the value doesn't change when different months/year is selected?

       

      Any help is much appreciated!

       

      Thanks

        • 1. Re: Question about Moving Calculation
          Joshua Milligan

          Sira,

           

          Welcome to the forums!   It's a great place with lots of people eager to help -- Tableau employees, customers, and partners.

           

          I've posted a workbook that demonstrates how to do this.  The trick is to use a table calculation as the filter. 

           

          When a field or row-level calculation is used as a filter, the filter will be calculated at the data source and table calculations will be applied after the data has returned to the Tableau data engine.  As you saw, this results in the table calculation not taking into account filtered values.

           

          However, when you use a table calculation as the filter, all the data needed is returned from the data source, the table calculations are computed, and then the table calc filters are applied.  That means that the table calculations will be performed for all the data and not just a filtered subset.

           

          Please let me know if you have any questions -- I'd be happy to answer them!

           

          Regards,

          Joshua

          • 2. Re: Question about Moving Calculation
            sira.sasitorn

            Joshua,

             

            Thank you for the response and advice!

             

            Regards,

            Sira

            • 3. Re: Question about Moving Calculation
              sira.sasitorn

              Hi Joshua,

               

              I have one more question regarding this topic. Is it possible  to make the table calculations a global filter instead of a regular filter?  I have another worksheet that is built on the same concept but on a different granularity (using Effective date instead of Date), so I couldn't add it into the same sheet without multiplying the rows. However, the year/month filter in the example above would be the driving factor, so I want to carry those filters into the new sheet (Sheet 2).

               

              Sheet 1 is from the example above

              Sheet 2 is the one where I want the Month/Year filter from Sheet 1 to carry over

              Granularity tab is to show you that each Date have multiple Effective Dates

               

              Regards,

              Sira