Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

10 Replies
Not applicable
Author

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

//O

Not applicable
Author

any ideas?

Not applicable
Author

anyone?

Digvijay_Singh

Please check if this helps -

settle.PNG

jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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)

Not applicable
Author

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

Capture.JPG

Not applicable
Author

null values im afraid