Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?