Finding the newest value

[#1]

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

[SunilChauhan]

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

[jagannalla]

Hello,

You can try this expression.

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

[#4]

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

[#5]

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]

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 · Accepted solution]

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

[#8]

Hi DV,

Simple and easy. Thanks.

/Jakob

[IAMDV]

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

[#10]

No problem.