Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Return the max month

Hey all,

I bring in a set of data that contains date placeholders for future data, i.e. For the current year the records all exist, but the values are  0

Month
Value

Jan

12
Feb41
Mar8
Apr24
May2
Jun

0

Jul0
Aug0
Sep0
Oct0
Nov0
Dec0


and there are a number of years in the future.

I have no issue capturing the month when it has been selected, however when none is selected, I am struggling to have a text box display Mar, since Mar is the current month.  I realise I can just use MONTH(Today()), but I also then need it to display the max month when i select a range of months.

I also need to capture the value of an expression for the current max month..

I've attached a reduced version of the report.  Whart I want the two text boxes to show is the Month, which is Mar, and the value for the 2011-12 value of Mar, which is 82.25%.  If you highlight Jan-Feb, then it should Show Feb, and the value for Feb. 

2 Replies
Sokkorn
Master
Master

Hi mgreen85,

Let try this expression for your text objects:

1. Show max month :

     =PICK(MAX(DateMonth),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

2. Show value for max month :

     =NUM(Avg({$<data_measure={'Actual'}, KPIDate = P(DateValue), month = {$(=PICK(MAX(DateMonth),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'))} >}data_value)/100,'###.##%')

See the sample attached file.

Let me know if this one help you.

Regards,

Sokkorn

Not applicable
Author

That works partly.

If I don't have a month selected though, and the current year is only populated to May like the above table, how do I make it identify May as the max month instead of December.

Also, since i'm working in the financial year, I need to switch December for June, how would I go about this?