11 Replies Latest reply on Aug 18, 2015 8:25 AM by Samuel Gee

# Calculated field to return the highest value

Hello!

I have 200 titles, each with sales across 300 weeks.

I would like to see the highest week's sales, per title.

So:

Title 1     Week 43     17,000

Title 2     Week 21     14,321

Title 3     Week 52     13,481

I'm sure there's some sort of calculated field that would let me do this easily, but I'm having trouble working out what to use. I tried with MIN and MAX and dabbling with an Index, but none of it really achieved much.

Sam

• ###### 1. Re: Calculated field to return the highest value

Hi,

We need to use partionnin and addressing here and to the max level of detail.Assuming u have more sales per title..Please post workbook i can help out

• ###### 2. Re: Calculated field to return the highest value

Thank you Venkatesh. A dummy workbook is attached.

I appreciate the help!

• ###### 3. Re: Calculated field to return the highest value

Hi Samuel!

Since you are on version 9 we can do this with an LOD calc like below:

{ EXCLUDE [Week] :

MAX( { INCLUDE [Week] :

SUM([Sales]) }

) }

Let me know how this looks

Thanks!

• ###### 4. Re: Calculated field to return the highest value

Thanks so much Dan! Certainly looks great.

I don't want to be cheeky, but if you have two more moments could you please explain to me what that code is doing - and also how I might get the specific week value displayed in the second sheet next to the highest value. (I don't need the sum of sales so much ).

Thank you!

Sam

• ###### 5. Re: Calculated field to return the highest value

I have done it in version 8 Attach workbook please have alook

1 of 1 people found this helpful
• ###### 6. Re: Calculated field to return the highest value

Hi Samuel!

The nested LOD calc is effectively replicating the value of a reference line.  Let's start with the 'Ref Line' worksheet in the attached workbook.  Here I've placed a reference line with a scope of Per Pane and set the value to Max Sales.

By using the nested LOD of first including the lowest level of our hierarchy (Title - Week), grabbing the Max Sales value, then wrapping that in an Exclude LOD, we are able to take the reference line value from our first worksheet and put that into a calculated field.

If we also want to show the week number then we can leave the Week dimension on the view and create a second calc to filter with:

IF SUM([Sales]) = ATTR([Max Title Sales])

THEN 1

ELSE 0

END

The reason we need to do this type of formula is that we can't filter with a boolean calc.  Hopefully this explains things a bit better.

Let me know if this version of the workbook is unclear.  Thanks Samuel!

1 of 1 people found this helpful
• ###### 7. Re: Calculated field to return the highest value

Thanks Dan, I really appreciate it.

Do you have a resource you can recommend for me to read up on LOD and how to create the type of formula you're using? I'm a pretty advanced Excel user, and I'm finding the syntax changes between that Tableau troubling.

For example, I'm now trying to create a view that will sum the top 8 week sales for each given title and display it, but it's...difficult, haha.

• ###### 8. Re: Calculated field to return the highest value

Thank you Venkatesh, I appreciate the time you put into that!

• ###### 9. Re: Calculated field to return the highest value

{ EXCLUDE [Week] :

MAX( { INCLUDE [Week] :

SUM([Sales]) }

) }

So in the above - just checking I understand - we're finding the maximum value for (Week : organised by sum of sales), and excluding all other weeks - leaving us with just the desired week in the view?

• ###### 10. Re: Calculated field to return the highest value
1 of 1 people found this helpful
• ###### 11. Re: Calculated field to return the highest value

Thank you Rody