Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bwbrodie
New Contributor

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

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

  

MonthUsed AAvail AUsed BAvail BUsed CAvail C
1/01/20161009010010100
1/02/2016610010020100
1/03/20161210080100100
1/04/2016181007510020100
1/05/2016241007010010100
1/06/2016301006510020100
1/07/2016361006010030100
1/08/2016421005510040100
1/09/2016481005010030100
1/10/2016541004510020100
1/11/2016601004010010100
1/12/2016661003510020100
1/01/2017721003010030100
1/02/2017781002510040100
1/03/2017841002010050100
1/04/2017901001510040100
1/05/2017961001010030100
1/06/2017100510020100
1/07/201710010010100
1/08/2017100100100
1/09/2017100100100
1/10/2017100100100
1/11/2017100100100
1 Solution

Accepted Solutions

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

try below

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


Regards

Great dreamer's dreams never fulfilled, they are always transcended.
3 Replies

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

try below

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


Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted
antoniotiman
Honored Contributor III

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

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

Note : = sign at the end is optional

bwbrodie
New Contributor

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

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