Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Showing results for 
Search instead for 
Did you mean: 

Using where condition with MAX() function

I am trying to create a variable that identifies the most recent fiscal month where I have ACTUAL cost data.  This seems like it should be very straightforward but I'm not a programmer and can't figure what I'm doing wrong syntactically.  I've been poking around these help pages and trying to apply the solutions but none are working.  I've tried things like this:

=Max(([Fiscal Month]) WHERE Sum(ACTUAL)>0)

When that didn't work, I tried set analysis:

= Max({<SUM(ACTUAL)= {">{0"}>}[Fiscal Month])

I'm attaching a table that shows what my summarized data looks like.  In this case, I want the variable to return "MAY" since that is the most recent month where the sum of the ACTUAL values exceeds zero.

I'm not using actual dates in this case.  My data includes a three character text string field, [Fiscal Month]; my load statement uses the DUAL function to assign the fiscal month number, 1-12, for sorting etc. So that is how I hope to obtain the MAX(), by using that numeric dual value.

I hope I stated everything clearly enough here to make sense.  Thanks a lot for your help!

Tags (2)
1 Reply
MVP & Luminary
MVP & Luminary

Re: Using where condition with MAX() function

I could imagine that you don't need a sum(ACTUAL) else just a check if there is any ACTUAL and then:

= Max({< ACTUAL = {'*'}>} [Fiscal Month])

should work.

- Marcus