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

    Getting Max Date for a Calculated Field

    Keyerra Richardson

      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.