12 Replies Latest reply on Feb 17, 2017 1:19 AM by Kir Mir

# Find the Max Value of a Sum

Hi,

I am sure that this is a very simple problem but I want to find the max value of a sum of values. I have provided an example in Excel where I want to sum by company and time period, and then I want to find the max and min in each period and show in a table.

It is very straightforward in Excel, I just cant grasp calculations in Tableau yet

Any help with this problem would be appreciated.

PC

• ###### 1. Re: Find the Max Value of a Sum

Use Level of Detail expressions, example: {INCLUDE : MAX([Your Measure])}

http://onlinehelp.tableau.com/current/pro/online/windows/en-us/calculations_calculatedfields_lod_overview.html

1 of 1 people found this helpful
• ###### 2. Re: Find the Max Value of a Sum

You're very close. You don't necessarily need your sum volume calculation, as this can be included in your table calculation (Calculation 1). Right now, you have the 2, 0 values in your table calculation, which cause the lookup to only look at the 2 values preceeding it, which is why you won't truly get the max value in the ENTIRE table. To remedy this, just adjust your Calculation 1 to be like the following:
WINDOW_MAX(SUM([Volume]))

This calculates the MAX value across the entire window. The value in this case being SUM([Volume]), thus it finds the maximum sum. Now, if you want to compute at a level that is not the one you're displaying (Company and time), then you will need to use Level of Detail expressions.

• ###### 3. Re: Find the Max Value of a Sum

Hi Patrick

Do you have v9? on higher?

The reason I ask, is that functionality to help, was only introduced from v9, I'm talking about Level of Detail (LOD).

PS. You can do it without v9, its just easier with

Cheers

Mark

• ###### 4. Re: Find the Max Value of a Sum

Hi Mark,

I am still down at v8 unfortunately.

• ###### 5. Re: Find the Max Value of a Sum

Hi Ben,

That is helpful, I am on version 8.2 so I do not have the LOD parameter.

PC

• ###### 6. Re: Find the Max Value of a Sum

Hi Patrick

Interesting problem, and helps explain why everyone went crazy when LOD was introduced

There are ways, with Tableau there normally is...

This is a quick hack, I'm sure it can be done better... the trade-off is that you need to display the hour/time.

Basically, I leverage the RANK function to order the values, as you want the MIN and MAX we sort them one way, take the first item, then...

reverse the sort, and again take the first item... clever, eh!

Anyway, here are the formulas -

MIN = IF RANK(SUM([Volume]),'asc') = 1 THEN SUM([Volume]) ELSE NULL END

MAX = IF RANK(SUM([Volume]),'desc') = 1 THEN SUM([Volume]) ELSE NULL END

I worked on your workbook but haven't provided it, because i have v9.2.4 you wont be able to open mine, hence the screenshots.

The workaround isn't perfect, but it gets you were you wanted to be, just.

If i can think of a better way (without v9), I'll let you know

Hope that's all clear

Cheers

Mark

2 of 2 people found this helpful
• ###### 7. Re: Find the Max Value of a Sum

Hi Patrick!

You can maybe create 2 calcs and set compute using > Time in the table calcs setting. Although like Mark says, this is WAY too easier post version 9.0

Min: window_min(sum([Volume]))

Max: window_max(sum([Volume]))

Place Time on level of detail, company on rows and double click on the 2 calcs to bring them on the view.

Then create a 3rd calc: LAST() = 0 and place it in filters so only the last value per company is visible in the view (because window_min and max will fill all rows with the same value for all time values there are). Click on the dropdown of the LAST() calc in the filter and make sure compute using is set to Time for this one as well.

1 of 1 people found this helpful
• ###### 8. Re: Find the Max Value of a Sum

Hi Pooja,

That is very helpful, but the figures are not coming out correctly for me. This is one of the reasons I hate table calculations as I find it difficult to trust them.

Do you know what I could be doing wrong?

• ###### 9. Re: Find the Max Value of a Sum

Hi Patrick!

Yes, I am not a fan of table calcs either especially if I have to use them after being used to LODs. You have to make sure addressing and partitioning are set correctly to make sure everything works well. I am not sure where you are getting the values from? For Kia, 190 and 17 are not even values of the volumes?

What are you using in the formulas for window min and max? Did you make sure compute using is set to > Time?

1 of 1 people found this helpful
• ###### 10. Re: Find the Max Value of a Sum

Hi Pooja,

Thanks for that. I will look at it again. I hope that by doing table calculations consistently I will final "get them". At least you understand them

PC

• ###### 11. Re: Find the Max Value of a Sum

Hi Patrick - table calcs are an important concept - they will let you do a lot of analyses that you can't otherwise. Especially if you aren't enjoying the power of v9 just yet. I've included a workbook with fairly detailed descriptions of what is going on in this example. Hope this helps your understanding.

1 of 1 people found this helpful
• ###### 12. Re: Find the Max Value of a Sum

Hi, help on the example of Ben Neville
how to show not only the maximum value for each company but also hour in which there was the maximum value

KIA 8 350
VOLVO 4 166

Thanks