5 Replies Latest reply on Sep 28, 2018 7:47 AM by Okechukwu Ossai

# Getting Max Date for a Calculated Field

Hi Everyone!  I searched the forum for this issue, but everything suggested I tried and still could not get past the annoying squiggly red lines.  I'm using desktop version 2018.1.  Here's my dilemma:

I created a calculated field to show the max funds employed for my data set.  This basically gives me the max balance booked in the GL for a specific Region.

{ EXCLUDE [Tran Date]:MAX(

{ FIXED [Region (cons.)],[Level8],[Tran Date]: ([YTD BAL - Act USD])}

)}

**NOTE:  YTD BAL is a calculated field as well since we went from a calendar year end to a fiscal year end this year**

What I'm trying to accomplish is identifying the date associated with this Max amount.  Every formula I try to use gives me an aggregate/non-aggregate error in the IF statement.  My initial thought was:

IF [YTD BAL - Act USD]=[Max Funds Employed] THEN [Tran Date] END

However that did not work.  I tried doing  fixed LOD, nesting formulas within the IF statement, a MAX formula, etc but nothing is simply returning the date for the max funds employed value.  After two hours I'm frustrated because it couldn't possibly be that complicated to get the date, however I am a newbie to Tableau.

• ###### 1. Re: Getting Max Date for a Calculated Field

It seems YTD Bal is pre-aggregated. If yes, then change the formula to

IF [YTD BAL - Act USD] = SUM([Max Funds Employed]) THEN [Tran Date] END

Check to see that YTD Bal and Max Funds are at the same level of detail. This is important to make the comparison work.

Hope this helps.

Ossai

• ###### 2. Re: Getting Max Date for a Calculated Field

Thanks for the quick response!  I did try that and I still got an aggregate/non-aggregate error.  Max Funds Employed is fixed to level of detail on Regions and Level.  Would I need to fix the YTD BAL to that same level of detail?  When I did this I got every single day of the year even with the aggregation set to Max.

• ###### 3. Re: Getting Max Date for a Calculated Field

Hi Keyerra,

This should take the error message away and hopefully give you the correct result. If not, you can fix the YTD BAL to the same level of detail.

IF [YTD BAL - Act USD] = SUM([Max Funds Employed]) THEN ATTR([Tran Date]) END

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 4. Re: Getting Max Date for a Calculated Field

This worked!  Once I fixed YTD BAL to the same level of detail it finally produced the single date I was looking for...thanks for your help!

• ###### 5. Re: Getting Max Date for a Calculated Field

You are welcome. Glad it helped.