Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I have what I think should be a relatively simple task to write a chart expression for, but have been unable to find a solution from searches. Hoping somebody can help.
I have a table with two columns /Fields - Baseline and Release_Date
Baseline Release_Date
2022 Dec BOD 4 20/11/2022
2022 Dec BOD 5 18/1/2023
I already have a Variable that returns the latest date, 18/1/2023, from the Release_Date Field - vLatest_Release_Date = Date(Max([Release_Date])) - and I need an expression that returns the '2022 Dec BOD 5' by 'looking up' the Variable in the Baseline Field
The Baseline Field format can have many different formats - the expression just needs to return what's there based on the Release Date.
Many thanks, Richard
I used this script
LOAD
Baseline,
date#(Release_Date, 'D/M/YYYY') as Release_Date
inline [
Baseline, Release_Date
2022 Dec BOD 4, 20/11/2022
2022 Dec BOD 5, 18/1/2023
];
and with that datamodel I was able to get "2022 Dec BOD 5" using FirstSortedValue(Baseline, -Release_Date)
See attached app
Try using the firstsortedvalue() function.
=FirstSortedValue(Baseline,-Release_Date)
Thanks for the suggestion but unfortunately doesn't work
Cheers, Richard
I used this script
LOAD
Baseline,
date#(Release_Date, 'D/M/YYYY') as Release_Date
inline [
Baseline, Release_Date
2022 Dec BOD 4, 20/11/2022
2022 Dec BOD 5, 18/1/2023
];
and with that datamodel I was able to get "2022 Dec BOD 5" using FirstSortedValue(Baseline, -Release_Date)
See attached app
Thank you, Vegar!
So the script 'date#(Release_Date, 'D/M/YYYY') as Release_Date inline' is converting the date to a numerical value, just like Excel does if you change a date to a Number format?
Thanks again for your help,
Richard
Yes, date#() i interpretation function. It interpretat the given string value (1st parameter) as a date with the given dateformat (2nd parameter).
@richard_gale Hello Richard, In case if you are looking for something at the script level. Please see the code below:
NoConcatenate
Temp:
Load Baseline, Date(Date#(Release_Date,'DD/MM/YYYY'),'DD/MM/YYYY') as Release_Date inline [
Baseline,Release_Date,0,
2022 Dec BOD 4,20/11/2022
2022 Dec BOD 5,18/1/2023
];
NoConcatenate
Temp1:
Load *
Resident Temp
Order by Release_Date Desc;
Let Vmax=Peek('Release_Date',0,'Temp1');
Drop table Temp;
NoConcatenate
Temp2:
Load Baseline
Resident Temp1
where Release_Date='$(Vmax)';
Drop table Temp1;
Exit Script;