Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

fgirardin
New Contributor III

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
MVP
MVP

Re: Display latest value using last date

May be this

Dimension

ITEM

Expression

FirstSortedValue(STAT, -[STATE DATE])

4 Replies
MVP
MVP

Re: Display latest value using last date

May be this

Dimension

ITEM

Expression

FirstSortedValue(STAT, -[STATE DATE])

fgirardin
New Contributor III

Re: Display latest value using last date

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     

MVP
MVP

Re: Display latest value using last date

May be just this

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

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

fgirardin
New Contributor III

Re: Display latest value using last date

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

Works great, Thanks !!

Community Browser