6 Replies Latest reply: Apr 17, 2015 11:33 AM by Jeff Held

# Writing an Expression that Limits results based on max Year and Max Date

I'm seeking help in writing an expression that will limit the results based on the max value date in two separate columns.  The first column is a Fiscal Year (2014, 2013, 2012...) and the second column contains a Date (10/31/2014, 11/30/2014, 12/31/2014).

What I currently have returns results for the max Fiscal Year:

I'd like to add in the max Date to this expression, but not clear on the syntax.  Any help would be appreciated.

• ###### Re: Writing an Expression that Limits results based on max Year and Max Date

Something like this?

Sum({\$<[Fiscal Year]={\$(=max("Fiscal Year"))},[Date]={\$(=Num(Max(Date)))}>} [Adjusted Fair Value])

Or do you want to use the max of one of the two columns?

• ###### Re: Writing an Expression that Limits results based on max Year and Max Date

So I have a column that contains a Fiscal Year and a column with a Date (Quarter End Date).  I want to see the data for a specific year and a specific Date (Quarter End).  I tried the expression above and it did not work.  What could I be doing wrong?

• ###### Re: Writing an Expression that Limits results based on max Year and Max Date

The Date alone is sufficient if it is a full date stored - with the year...

• ###### Re: Writing an Expression that Limits results based on max Year and Max Date

Ok, so would the expression look like this:

• ###### Re: Writing an Expression that Limits results based on max Year and Max Date

That will get you values for exactly the Max(Date) ... If you want up to and including Max(Date) it has to be

like this:

Sum( { \$< Date={ "<=Num(Max(Date))" } > }  [Adjusted Fair Value] )

remember to count your brackets/ellipsis/paranthesis to get them matched or the expression will fail...

• ###### Re: Writing an Expression that Limits results based on max Year and Max Date

Petter,

Thanks for your help on this.  I need to revisit how my data is structured and will comeback to you on this one.  I don't think I need two date columns when one will suffice.