Expression to find last non NULL value in Field regardless of current selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression to find last non NULL value in Field regardless of current selection

PrashantSangle — Thu, 16 Nov 2017 08:36:35 GMT

try below

=Max({1<[Used C]={"<>ISNULL()"}>} Month)

Regards

Re: Expression to find last non NULL value in Field regardless of current selection

antoniotiman — Thu, 16 Nov 2017 08:46:14 GMT

=Max({<[Used C]={"<>ISNULL()"},Month=>} Month)

Note : = sign at the end is optional

Re: Expression to find last non NULL value in Field regardless of current selection

Anonymous — Thu, 16 Nov 2017 22:53:28 GMT

A big thank you to Prashant and Antonio. Much appreciated!

topic Re: Expression to find last non NULL value in Field regardless of current selection in QlikView

Expression to find last non NULL value in Field regardless of current selection

Re: Expression to find last non NULL value in Field regardless of current selection

Re: Expression to find last non NULL value in Field regardless of current selection

Re: Expression to find last non NULL value in Field regardless of current selection