2 Replies Latest reply on Mar 20, 2018 3:32 PM by Ryan Miller

    Rolling Averages & Indexes

    Ryan Miller

      Hi,

       

      I'm having some trouble calculating some things in Tableau to create a rolling average (using it as the base of my index) and then my index calculation to measure against it.

       

      Ideally, what I'd like to have is a 12 month rolling average, and then be able to index against that, no matter the lowest denominator of date for my data.


      So I have data at the individual date level that I'd be keeping at daily, as well as rolling up to monthly, that i'd like to measure against that 12 month rolling average and have the index change, no matter if it's at a daily or monthly level.


      (Sorry if none of that makes sense and is too wordy. Ha)

       

      So, i've done an "Add Calculation" to be able to get a moving average for 12 periods (months) back, and I've also used a DATEIFF equation to try and get a 12 month average. However, i'm struggling with the following:

      • The Add Calculation method can't be combined into a calculated field to use it as a base to generate an index off of
      • The DATEIFF method doesn't adapt with my changing of the Date dimension when I go from month to daily

       

      Any thoughts on how to accomplish this?

       

      Perfect situation: I'll have a Calculated Field that represents a rolling 12 month average number (for each metric) and then i'll be able to create an index calculated field that can be rolled up and down by date (a single day of data can be measured against the rolling 12 month average, as well as a single month).

       

      Thank you for any and all help!

        • 1. Re: Rolling Averages & Indexes
          Jim Dehner

          Hi Ryan

           

          Not certain what you are trying to do but see the attached

          first calculation is the R12

          then

          the index would be

           

           

          but you referenced the Average R12 ? average over what period? Help me understand what you really want

           

          well the first 2 calculations will return 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.

          • 2. Re: Rolling Averages & Indexes
            Ryan Miller

            Hey Jim,

             

            First, thanks so much for jumping in and helping out! Second, sorry I didn't explain this well enough in my first post. Ha

             

            So what you provided is perfect for when things are at a monthly level--it gives me that rolling average and the index looks great. However--and what I was failing to explain well--when I break it out at the daily level, it warps the rolling average and index.

             

            What I'd like to accomplish is keeping the rolling average against the previous 12 months, but have the index apply to the rolling 12 month average no matter if the data is at the month or daily level.


            I THINK that what's happening is that it's looking at periods across the table, so when I break it out to day, it's looking at the previous 12 days for an average. Is that what's happening? Any thoughts or ideas?

             

             

             

            Screen Shot 2018-03-20 at 6.11.41 PM.png