3 Replies Latest reply on Sep 15, 2017 6:08 AM by Doug Stanley

    Calc to evaluate the max of an average?

    Doug Stanley

      I have a chart that shows how far "Jane" runs on average by day of the week:

      The average lines tell me that, by a slight margin, she runs farther on Saturdays than any other day of the week.

       

      As this data will change over time, I'm trying to write a calculation that would simply return the name of the day with the max average, in this case "Saturday", so I can use it in the chart tile. I can't quite seem to get there. Any help appreciated.

       

      Workbook (10.3) attached.

       

      Thanks,

      Doug

        • 1. Re: Calc to evaluate the max of an average?
          Kalyan Allam

          Hi,

           

          I cant open your file as i am still at Tableau 9.3. So, I made a sample with superstore data with a similar scenario. Here , i wanted to see which Weekday has Maximum Sales.

           

          Step1: Create a cacluation to calculate Sales for each Weekday.

           

           

          we are doing this so thagt we can compare the Sales of one weekday to other.

           

          Step 2: Find the max sales of all the weekdays.

           

          The above calc will give the max sales from the weekday sales.

           

          Step 3:  Write a True/False calc to filter out the max sales.

           

           

          I am attaching the twbx., Please let me know if this helps you. There is another way of doing it using Table Calculations. We can explore that as well if this solution doesnt suit you.

           

          Thanks,

          Kalyan Allam

          • 2. Re: Calc to evaluate the max of an average?
            Simon Runc

            hi Doug,

             

            So there might be a simpler way, but here's one that works...we can nest all this into one calculation, but thought I'd break it out to help the understanding.

             

            So first I've created an LoD version of your Reference Lines (as I'm using FIXED LoDs I also needed to add all your filters to "in context", so that they are implemented before the LoD is calculated)

             

            [highest average by Day LoD]

            {FIXED [Day]: AVG

            (

            {FIXED DATETRUNC('day',[Finish]): SUM([Distance (miles)])}

            )

            }

             

            Next we need one to grab the MAX of this

             

            [highest average]

            {MAX([highest average by Day LoD])}

             

            btw when we just want a global LoD aggregation (ie no dimension in the left hand expression we can just omit the FIXED, but we can include it too...Tableau doesn't care!)

             

            and then finally we want to compare these 2 and return the Day, for our title

            [Day of the week with the highest average]

            {MAX(IIF([highest average by Day LoD] = [highest average],[Day],NULL))}

             

            and that's it!

             

            Hope that does the trick, and makes sense.

            • 3. Re: Calc to evaluate the max of an average?
              Doug Stanley

              This is awesome, Simon. Many thanks (and especially for the explanation).

               

              Doug