FirstSortedValue, find last value before (or equal to) 08:00

[#1]

Hi,

need to find the last value before (or equal to) 08:00 but the below expression does not work properly, any ideas?

Pls find attached example files.

FirstSortedValue({<SettlementTime={'<$(=time(08:00:00:000000))'}>} RunningBalance,-SettlementTime)

Rgds,

Olle

[#2]

The expression above only picks the very last value for each day, regardless of the time.

//O

[#3]

any ideas?

[#4]

anyone?

[Digvijay_Singh]

Please check if this helps -

[jonathandienst]

I assume that SettlementTime has no date component (put num(SettlementTime) in a list box - the values should be between 0 and 1). If so, you could try this:

FirstSortedValue({<SettlementTime={"<=$(=time#('08:00:00'))"}>} RunningBalance, -SettlementTime)

[#7]

Looks good! However not sure how to implement in my rather nested production script, the app provided is only a simplified version.

[Kushal_Chawda]

try this

=FirstSortedValue({<SettlementTime={"<$(=time#('08:00:00:000000','hh:mm:ss:fffff'))"}>} RunningBalance,-SettlementTime)

[#9]

This is what I got using num(SettlementTime) in the list box. Weird.....

[#10]

null values im afraid