
1. Re: Calc to evaluate the max of an average?
Kalyan Allam Sep 15, 2017 5:47 AM (in response to Doug Stanley)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

maxWeekdaySales.twbx 1.2 MB


2. Re: Calc to evaluate the max of an average?
Simon Runc Sep 15, 2017 5:47 AM (in response to Doug Stanley)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.

Highest Milage Day in Title.twbx 508.9 KB


3. Re: Calc to evaluate the max of an average?
Doug Stanley Sep 15, 2017 6:08 AM (in response to Simon Runc)This is awesome, Simon. Many thanks (and especially for the explanation).
Doug