Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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:

Sum({$<"Fiscal Year"={$(=max("Fiscal Year"))}>}[Adjusted Fair Value])

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

1 Solution

Accepted Solutions
MVP
MVP

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...

6 Replies
MVP
MVP

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?

Not applicable

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?

MVP
MVP

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...

Not applicable

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

Ok, so would the expression look like this:

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

MVP
MVP

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...

Not applicable

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.

Community Browser