7 Replies Latest reply on Sep 16, 2016 8:42 AM by Alex Braun

    Rolling 37 months

    Vishal D

      I have a Report Year  = [2010, 2011, 2013, 2014, 2015, 2016] & Report Month = [1,2,3,4,5,6,7,8,9,10,11,12] as fields in my data source.

       

      Based on todays date I want to calculate rolling 37 months inclusive of current month and then filter the data on the worksheet to only show for those 37 months.

       

      How do I calculate rolling 37 months and pass to Report Year and Report Month fields?

        • 1. Re: Rolling 37 months
          Joe Oppelt

          Probably lots of ways to do that, but I usually create a calc that filters for index >= window_max(index())-36

           

          Put that on your filter shelf.  The whole table gets computed, but Tableau just displays the last 37 buckets.  (This will preserve any table calcs such as running sums, BTW, even though it only displays the last 37 slots.)

          • 2. Re: Rolling 37 months
            John Brink

            Here's the way I do a 30 day...and as you can see you can filter first 30 days, day 31-day 60, day 61-90, etc. I have 6 selections of period to choose from

             

            You just would have to change the -30  to 37 in my calculation.  Note, I'm also allowing the user to choose which date will be "start" of the first rolling day period. Note -  [DATE_end of rolling 30] is a date parameter.

             

            IF [Date] <= [DATE_End of Rolling 30] and [Date]
            >= [DATE_End of Rolling 30]- 30 THEN "0-30 Days"
            //
            ELSEIF
            [Date] <= [DATE_End of Rolling 30] - 31 and [Date]
            >= [DATE_End of Rolling 30]- 60 THEN "31-60 Days"
            //
            //
            ELSEIF
            [Date] <= [DATE_End of Rolling 30] - 61 and [Date]
            >= [DATE_End of Rolling 30]- 90 THEN "61-90 Days"
            //
            //
            ELSEIF
            [Date] <= [DATE_End of Rolling 30] - 91 and [Date]
            >= [DATE_End of Rolling 30]- 120 THEN "91-120 Days"

            ELSEIF
            [Date] <= [DATE_End of Rolling 30] - 121 and [Date]
            >= [DATE_End of Rolling 30]- 150 THEN "121-150 Days"

            ELSEIF
            [Date] <= [DATE_End of Rolling 30] - 151 and [Date]
            >= [DATE_End of Rolling 30]- 180 THEN "151-180 Days"
            ELSE
            NULL
            END

            • 3. Re: Rolling 37 months
              John Brink

              More simplified. Move to filter and choose T

               

              • 4. Re: Rolling 37 months
                Vishal D

                I created calc field 'rolling 37 months' = index() >= window_max(index())-36

                 

                placed it into filter and marked it 'True'

                 

                The results still show all years/ months data and not just last 37 months based on current date.

                 

                I want to calculate filter based on today's date and calculate last 37 months and apply that.

                 

                I could have some bad data which has future year/ month e.g 2025 data. based on window _max logic it will use last 37 months based on result set and not on the today's date.

                • 5. Re: Rolling 37 months
                  Vishal D

                  I want last 37 months and not days.

                   

                  Also I dont have date field. I have [Report year] & [Report Month'

                  • 6. Re: Rolling 37 months
                    Joe Oppelt

                    Vishal -- Can you upload a packaged workbook?  you may have to edit the table calc to count the index values along the proper dimension.  I can show you.

                     

                    Or there are other ways to use a table calc in the filter like that.  (LOOKUP() is something that you can use here.)  That would especially be useful if you have dates greater than TODAY().

                    • 7. Re: Rolling 37 months
                      Alex Braun

                      I would combine the two together to make a date field

                       

                      date([Report Month] + '/1/' + [Report Year])

                       

                      and then put that calculated field on the filters shelf and choose relative date and then you should be able to get a rolling 37 months.