Display latest value using last date

[fgirardin]

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

[sunny_talwar · Accepted solution]

May be this

Dimension

ITEM

Expression

FirstSortedValue(STAT, -[STATE DATE])

[fgirardin]

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]

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

Works great, Thanks !!

[rafael_qg]

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