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: 
fgirardin
Creator
Creator

Display latest value using last date

Hello everyone !

I have a table containing order N°, Order date, Item, State, State Date

I would like to be able to display only the latest STATE of the ITEM based on the date

At the moment, the pivot table looks like this

___________________________________________________________

    ORDER N°          ITEM          STATE          STATE DATE

                                AAA1            403                    01.01.2018

      0001                  AAA1            407                    05.01.2018

                                AAA2            403                    06.01.2018

                                AAA3            403                    02.01.2018         

                                AAA3            407                    03.01.2018

__________________________________________________________

     0002                  BBB1            403                    06.01.2018

                                BBB1            407                    08.01.2018   

                                BBB2            401                    31.12.2017

                                BBB2            403                    02.02.2018 

___________________________________________________________

The table should only display States that are the most recent >

AAA1     407

AAA2     403

AAA3     407

BBB1     407

BBB2     403

               

Thank you for your help

1 Solution

Accepted Solutions
sunny_talwar

May be this

Dimension

ITEM

Expression

FirstSortedValue(STAT, -[STATE DATE])

View solution in original post

5 Replies
sunny_talwar

May be this

Dimension

ITEM

Expression

FirstSortedValue(STAT, -[STATE DATE])

fgirardin
Creator
Creator
Author

Perfect !
Thank you

How can I change background color using this expression ?

Obviously, if I try to put a if statement on the STATE (if STATE='401'....) it won't work as each ITEMS have 2 or more STATE

How to use the Expression to check the value, then the color of the cell ?

Thanks again     

sunny_talwar

May be just this

If(FirstSortedValue(STAT, -[STATE DATE]) = 401, ...,

If(FirstSortedValue(STAT, -[STATE DATE]) = ..., ...

fgirardin
Creator
Creator
Author

if(FirstSortedValue(State, -[State_date])='401',rgb(222,0,0))

Works great, Thanks !!

rafael_qg
Partner - Contributor III
Partner - Contributor III

Hi, 

I have the same question and your solution works fine but the performance with a big table is very bad.

There is another solution for this case? Perhaps, order the table on script and another function with a better performance?

Thanks a lot