topic Return the max month in QlikView

Return the max month

Tue, 08 May 2012 06:34:49 GMT

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
Feb	41
Mar	8
Apr	24
May	2
Jun	0
Jul	0
Aug	0
Sep	0
Oct	0
Nov	0
Dec	0

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.

Re: Return the max month

Sokkorn — Tue, 08 May 2012 09:34:52 GMT

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

Return the max month

Wed, 09 May 2012 02:29:00 GMT

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?