Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Finding the newest value

Hi,

I have a simple table consisting of 3 fields (Container, Status and reload date). Every day the table is expanded with an extra day.

I would like to make a object that shows me the newest status for each container.

See attached file for details.

The result should be:

Container      Status

A                  1

B                  3

C                  4

Thanks.

BR

Jakob Hjorth-Hansen

1 Solution

Accepted Solutions
IAMDV
Luminary Alumni
Luminary Alumni

Jakob - Instead of following Set Analysis approach you can solve this by using FirstSortedValue function. Here is what you need...

FirstSortedValue( Status, -[Reload date])

I have used the minus sign before [Reload date] to sort in desc order or last sorted value instead of first sorted value.

I understand itss hard - not to think of Set Analysis but sometimes we need to step back to get things working.

I hope this helps!

Cheers,

DV

www.QlikShare.com

View solution in original post

13 Replies
SunilChauhan
Champion
Champion

Create a variable

vlastReload=max([Reload date]) in control+alt+v

in expression you can write below

maxstring(if([Reload date]=vlastReload,Status))

see the attached file

hope this helps

Sunil Chauhan
jagannalla
Partner - Specialist III
Partner - Specialist III

Hello,

You can try this expression.

=max({<[Reload date]={"=MaxString([Reload date])"}>} Status)

Not applicable
Author

Hi Jagan,

Thank you for the answer but your formula doesn't give me the correct answer.

I expect:

A 1

B 3

C 4

but I get

A 2

B 3

B 4

seems like it takes the highest value?

/Jakob

Not applicable
Author

Hi Sunil,

Thank you for the answer - it works, but I was not specific enough.

There can be a situation where the container is no longer in the list, but I still want to see the value from when it was uploaded last time.

I have added a container D to my example, where the last update was on the 28th. I still need to see it on my list with status 2.

Thanks for your interest.

BR.

Jakob

jagannalla
Partner - Specialist III
Partner - Specialist III

Ok according to sunil example. You want to see D value as null even there is no data for that at max date . If this in the case you need uncheck Supress zero values in Presentation tab.

IAMDV
Luminary Alumni
Luminary Alumni

Jakob - Instead of following Set Analysis approach you can solve this by using FirstSortedValue function. Here is what you need...

FirstSortedValue( Status, -[Reload date])

I have used the minus sign before [Reload date] to sort in desc order or last sorted value instead of first sorted value.

I understand itss hard - not to think of Set Analysis but sometimes we need to step back to get things working.

I hope this helps!

Cheers,

DV

www.QlikShare.com

Not applicable
Author

Hi DV,

Simple and easy. Thanks.

/Jakob

IAMDV
Luminary Alumni
Luminary Alumni

Jakob - Very soon, I'm going to make a video post on FirstSortedValue. Is it okay to use this example for demonstration?

Thanks,

DV

www.QlikShare.com

Not applicable
Author

No problem.