1 Reply Latest reply on Feb 5, 2019 7:49 AM by patrick.byrne.0

    Average last 3 months

    AJ.Yusuf

      Hi All,

       

      Hope you are well.Happy New Year!

       

      I have small issue. I have a monthly Bottom 20 report in staff memebers by Deals per day (Deals / Days Worked). That report is for say 2019 January (so far). I need a number beside whcih takes the Aveage of that paticular Employee (Staff Number) and does a Average for 3 months.

       

      I tired to use this formula but not working as you see the coloumn on the worksheet

       

      Effectively I need the aveage to be the average for (Dec 2018,Nov 2018 and Oct 2018). ONly if average is >0. For example next month in feb 2019 will be tthe average for Jan 2019,dec 2018 and nov 2018.

       

      I ama bit stuck

       

       

      Worksheet

        • 1. Re: Average last 3 months
          patrick.byrne.0

          Hello Junaid,

           

          I was reviewing the workbook and found a couple things that are throwing off the number off slightly. Using the Lookup function does work, but the view is displayed with only the bottom 20 showing, as stated above, the calculation is being performed on the values that are seen on the worksheet. They are not being calculated across the last 3 months. but the last 3 days. That is why we see the column of nulls stop after 3 (see image below, red arrow) and then starts returning values.

           

           

          Do not fret! The desired calculation can be achieved with some help of some great resources and a LOD calculation. I would start with the discussions below and please reach out if there is a trip up trying to implement one of the solutions.

           

          + Calculate average last 3 months?

          + LOD/Calculated Field to show Last 3 months data at Date level

           

           

          I am also going to try to put together a sample packaged workbook with a solution, but I wanted to provide this information first! Hope this helps!

           

          Cheers,

          Byrne, Patrick

          Community Manager

          Tableau Software