Case 000 How to add filter to get the lastest date stock amount

[paulyeo11]

Hi All

i Have below raw data.

GL_TABLE:
LOAD * INLINE [
GL_CODE,GL Desc,Doc No,Amount,date
10,STOCK,1,4,14/4/2019
10,STOCK,2,4,14/4/2020
20,REVENUE,3,6,1/4/2020
10,STOCK,4,69,23/4/2020
20,REVENUE,5,9,24/4/2020
];

I need to find out the lastest stock value from Table , it should be 69.

I use below expression , i get 9.
=FirstSortedValue(Amount,-date)

May i know how to add filter for GL_CODE = 10 in above expression , so that i can get 69 ?

I try below , it does not work :-
=FirstSortedValue(if(GL_CODE=10,Amount,-date))

Paul

[rubenmarin · Accepted solution]

Hi, you can try: =FirstSortedValue({<GL_CODE={10}>}Amount,-date)

[paulyeo11]

Hi Sir

Hi Sir

Your expression work on sample data/

When i apply your expression into my actual GL data .

=FirstSortedValue({<[GL Code]={2001010}>}Amount,-date)

i get null value/

Enclosed my QVW file.

Paul

[rubenmarin]

Hi, you have many different values for the same and firtsortedvalue only returns a value when there is only one value to returne.

Which is the expected result and why? 1.4015.660.88 because is the sum the stocks on 24/04/2020 which is the higher overall data?