Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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.
Hi there,
Have you ticked Supress When Value Is Null feature?
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:
How to use - Master-Calendar and Date-Values
- Marcus
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:
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.
Yes, they are expressions as well.
After testing your solution, it works as intended !
Thanks a lot !!