Null value returned when selecting latest date

[fgirardin]

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.

[#2]

Hi there,

Have you ticked Supress When Value Is Null feature?

[marcus_sommer]

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

[swuehl · Accepted solution]

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.

[fgirardin]

Yes, they are expressions as well.

After testing your solution, it works as intended !

Thanks a lot !!