Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Null value returned when selecting latest date

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

F.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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.

View solution in original post

4 Replies
Highlighted
Not applicable

Hi there,

Have you ticked Supress When Value Is Null feature?

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
MVP
MVP

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.

View solution in original post

Highlighted
Creator
Creator

Yes, they are expressions as well.

After testing your solution, it works as intended !

Thanks a lot !!