4 Replies Latest reply on Mar 21, 2013 2:16 PM by Kelly Pearce

    Two calculated fields based on different date ranges

    Kelly Pearce

      I'm trying to create two calculated fields; one is for the average of my monthly rate for the recent 6 months and the average of my monthly rate for the last 18 months. I then want to take the two averages and compare them using a KPI. I can't figure out how to create the two calculated fields on the same sheet for the different date ranges I indicated. I've attached a Tableau workbook with test data. Thanks

       

      --Sorry, was in a rush and thought I attached the file as a twbx. My bad, thanks Stegmaier for pointing out! cheers

        • 1. Re: Two calculated fields based on different date ranges
          Son Stegmaier

          Hi Kelly,

           

          Would you be able to attached the sample workbook as a twbx file?  i tried opening the twb but there's no data.

           

          Thanks.

          • 2. Re: Two calculated fields based on different date ranges
            Kelly Pearce

            Hi Stegmaier, attached the twbx file. My bad, thought I had. Thanks for looking!

            • 3. Re: Two calculated fields based on different date ranges
              Son Stegmaier

              This might work for you.  The window_avg function:

               

              Last 6 months (including the current month) - WINDOW_AVG(sum([Infections]/[Line Days]),-5,0 )*1000

              Last 18 months (including the current month) - WINDOW_AVG(sum([Infections]/[Line Days]),-17,0 )*1000

              • 4. Re: Two calculated fields based on different date ranges
                Kelly Pearce

                Hi Stegmaier,

                 

                Thanks for your respone. The formula looks logical, but I don't see that it's taking only the sum of the recent 6 months and giving me the average? Maybe I'm missing something here? I checked out the formulas in your attached Tableau workbook, but depending on how the Month/Year is filtered, it's showing an average for every month, not just the recent 6 months (which isn't much different from my Monthly Rate). In theory, any months before the recent 6 should be blank, because I don't want to calculate on any other months but the recent 6 (Aug2012-Jan2013). Your formula is also giving me a total result much larger than what it should be:

                 

                For example: As you can see in the images below, the result of the average of the recent 6 months is 4.95, not 29.814717 as indicated in your "Last 6" - it's adding all the rates together. I'm trying to figure out a formula that will capture this total recent 6 month average (4.95) and the total 18 month average prior to the recent 6 months (4.63) as distinct numeric fields so I can then write a formula for KPI that will compare the two (6mon) 4.95 and (18mon prior to recent 6) 4.63 - Because the rate for the recent 6 months is greater than the 18mon prior to the recent 6, I would use a KPI indicating no improvement.

                 

                Correct me if I'm missing something. Thanks for trying to help me out here! I'm stumped!

                 

                Test Data - recent 6 months.JPGTest Data - 18mon prior to recent 6mon.JPG