topic Re: Null value returned when selecting latest date in QlikView

Null value returned when selecting latest date

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

Hello everyone,

I'm working on an item list wich contains fields like "Key", "Price Code", "Price", "Start Date", "End Date", ...

I have a slight issue when I want to only display the row containing the last date (either be start or end date)

My table look like this:

KEY PRICE CODE PRICE START DATE END DATE

AAA PA 100 10.10.2014 11.10.2014

AAA PV 150 04.12.2015 04.05.2015

BBB PA 125 01.01.2012 01.02.2012

BBB PA 130 02.03.2013 01.02.2014

CCC PV 100 03.03.2013 04.04.2014

CCC PV 100 02.01.2016 -

With some help, I was able to create a toggle to show only the latest START DATE

My START DATE is both a Dimension and an Expression that I toggle using a variable (vViewMode) with 2 states: Date / Dates

The Dimension is shown when I want to see all the dates and the expression, only when I want the latest.

My expression for START DATE is

Date(Max(DATEFIELD))

and the condition is

vViewMode = 'Date'

It works fine except that I get a NULL result in all fields that have different values

In my example, if I would select only the latest date, the result would look like this:

KEY PRICE CODE PRICE START DATE END DATE

AAA - - 10.10.2014 -

BBB PA - 02.03.2013 -

CCC PV 100 02.01.2016 -

Note that each field that contained different data now show "NULL" (Price Code for AAA, Price and End Date for both AAA and BBB)

I tried to add END DATE as a dimension as well, using the same variable as condition. Nothing changed, it still displays "NULL"

Is there a way to show the full row containing the latest START DATE field ?

Thanks for your help

Re: Null value returned when selecting latest date

Wed, 27 Apr 2016 04:28:08 GMT

Hi there,

Have you ticked Supress When Value Is Null feature?

Re: Null value returned when selecting latest date

marcus_sommer — Wed, 27 Apr 2016 08:48:49 GMT

I don't understand what do you want to do with these latest date-selection and which results do you expects for it but usually it's quite helpful to match the start/end-date with a single date-field and this with a master-calendar:

IntervalMatch

How to use - Master-Calendar and Date-Values

- Marcus

Re: Null value returned when selecting latest date

swuehl — Wed, 27 Apr 2016 09:20:15 GMT

I assume your columns for PRICE CODE, PRICE, END DATE are also expressions, not dimensions, right?

The reason why yo then get NULL is that there is no unique return value for these expressions:

Use Aggregation Functions!

You can try FirstSortedvalue() to get the values for the latest DATEFIELD value:

=FirstSortedValue([PRICE CODE], -DATEFIELD)

=FirstSortedValue([PRICE], -DATEFIELD)

=FirstSortedValue([END DATE], -DATEFIELD)

This should work with your START DATE showing all or only latest dates.

Re: Null value returned when selecting latest date

fgirardin — Fri, 29 Apr 2016 07:45:55 GMT

Yes, they are expressions as well.

After testing your solution, it works as intended !

Thanks a lot !!