Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik Community
I am trying to create an expression to find the last non NULL value in a Field regardless of any current selection.
Using the following sample data, my objective is to identify the relevant Month with the desired results being :-
Used A - 1/05/2017 Used B - 1/06/2017 Used C - 1/07/2017
The example expression
=Max({<[Used C]={"<>ISNULL()"}>} Month)
is correct where Month is not a current selection however as soon as a selection is made for Month the results are by their nature limited to that selection and not what I am looking for. I have not yet discovered the strategic placement of "{1}" if that is the solution.
Any enlightenment would be greatly appreciated.
Thanks
Brian
Month | Used A | Avail A | Used B | Avail B | Used C | Avail C |
1/01/2016 | 100 | 90 | 100 | 10 | 100 | |
1/02/2016 | 6 | 100 | 100 | 20 | 100 | |
1/03/2016 | 12 | 100 | 80 | 100 | 100 | |
1/04/2016 | 18 | 100 | 75 | 100 | 20 | 100 |
1/05/2016 | 24 | 100 | 70 | 100 | 10 | 100 |
1/06/2016 | 30 | 100 | 65 | 100 | 20 | 100 |
1/07/2016 | 36 | 100 | 60 | 100 | 30 | 100 |
1/08/2016 | 42 | 100 | 55 | 100 | 40 | 100 |
1/09/2016 | 48 | 100 | 50 | 100 | 30 | 100 |
1/10/2016 | 54 | 100 | 45 | 100 | 20 | 100 |
1/11/2016 | 60 | 100 | 40 | 100 | 10 | 100 |
1/12/2016 | 66 | 100 | 35 | 100 | 20 | 100 |
1/01/2017 | 72 | 100 | 30 | 100 | 30 | 100 |
1/02/2017 | 78 | 100 | 25 | 100 | 40 | 100 |
1/03/2017 | 84 | 100 | 20 | 100 | 50 | 100 |
1/04/2017 | 90 | 100 | 15 | 100 | 40 | 100 |
1/05/2017 | 96 | 100 | 10 | 100 | 30 | 100 |
1/06/2017 | 100 | 5 | 100 | 20 | 100 | |
1/07/2017 | 100 | 100 | 10 | 100 | ||
1/08/2017 | 100 | 100 | 100 | |||
1/09/2017 | 100 | 100 | 100 | |||
1/10/2017 | 100 | 100 | 100 | |||
1/11/2017 | 100 | 100 | 100 |
try below
=Max({1<[Used C]={"<>ISNULL()"}>} Month)
Regards
try below
=Max({1<[Used C]={"<>ISNULL()"}>} Month)
Regards
=Max({<[Used C]={"<>ISNULL()"},Month=>} Month)
Note : = sign at the end is optional
A big thank you to Prashant and Antonio. Much appreciated!