10 Replies Latest reply on Jun 29, 2018 11:37 AM by Gerardo Varela

# Simple Calculation? Max String Possible?

Howdy all!

Concept of a Max String sounds goofy just saying it but I have a year column in my data for Fiscal Years.

Right now I see 2017, 2018, 2019. The user has requested these years be columns that appear as "FY 2017" "FY 2018" "FY 2019 YTD".

For the "FY 2019 YTD" to be dynamic I need Tableau to know that 2019 is the highest/max/last value to attach a "YTD" to the end.

I've been playing with the calculations a bit but attempting to mix aggregates with strings and integers that need to output strings has been a mess lol.

Does anybody have any advice or ideas by chance?

Thanks!

• ###### 1. Re: Simple Calculation? Max String Possible?

Formula

"FY " + STR(YEAR([Order Date]))
+ IIF(YEAR([Order Date]) = { MAX(YEAR([Order Date])) }," YTD", "","")

Domain (4 members)

 FY 2015 FY 2016 FY 2017 FY 2018 YTD

• ###### 2. Re: Simple Calculation? Max String Possible?

Hi Jonathan,

So Tableau is smart enough to recognize FY 2018 as a date, at least for an excel data source.  Once you connect to your data then change the your dimension to date:

Then after that put the dimension as a filter an make sure you click filter to latest value. This is newish feature, but I can't recall what version it was implemented on.

Attached workbook, vs 10.5, and excel source.  Let me know if that works for you or doesn't.

Regards,

Gerardo

• ###### 3. Re: Simple Calculation? Max String Possible?

Hi Jonathan

is the field actually a string field?  if so max() will take the alphabetic max value - since your records are "FY 2018" etc the "max year value" should also the max alphabetic value

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Simple Calculation? Max String Possible?

The following should work:

If attr(year([Order Date])) = ATTR({max(year([Order Date]))}) then "FY "+ str(attr(year([Order Date]))) + " YTD"

else "FY "+ str(attr(year([Order Date])))

end

• ###### 5. Re: Simple Calculation? Max String Possible?

Hi Jonathan,

Thanks

Deepak

1 of 1 people found this helpful
• ###### 6. Re: Simple Calculation? Max String Possible?

Thanks so much for all the replies all!

I'm sure there was many ways to solve this.

Some used YEAR() calculations, but my field was truly just a string dimension of "2017" "2018" "2019" etc. And it appears that YEAR() works with Date/Datetime data types.

I could not test all the responses but Deepak's solution did work for me.

Thanks again!

• ###### 7. Re: Simple Calculation? Max String Possible?

• ###### 8. Re: Simple Calculation? Max String Possible?

Actually Deepak (or anyone else good with calculation) you in-advertently fixed my original calculation that I could not get to work! lol.

My original code is below except I DID NOT have the {} around my MAX statement. If I remove the {} then the calculation breaks.

What are those brackets doing? I am unsure why adding them has the calc work seemlessly and without it breaks.

IF STR([FISCAL_YEAR]) = STR({MAX([FISCAL_YEAR])})

THEN STR([FISCAL_YEAR]) + " YTD"

ELSE STR([FISCAL_YEAR])

END

• ###### 9. Re: Simple Calculation? Max String Possible?

{} are Notations for LOD and {MAX(year)} is Looking in your Year Column and Is giving you 2019 which is maximum value of entire Column

Hope it Clarifies

Thanks

Deepak

• ###### 10. Re: Simple Calculation? Max String Possible?

Hi Jonathan,