How to compute Stock amount pick the latest date Stock Amount ?

[paulyeo11]

Hi All

My raw data at Table 1 .

SOURCE GL_CODE_ GL_DESC_ Amount date
-
PM..... 2001010..... STOCK..... 18.00 ....21/09/2019
PM..... 2001010..... STOCK..... 14.00 ....21/10/2019
PM..... 2001010..... STOCK..... 19.00 ....21/11/2019
PM..... 2001010..... STOCK..... 22.00 ....21/12/2019
PM..... 2001010..... STOCK..... 14.00.... 21/01/2020
PM..... 2001010..... STOCK..... 12.00.... 21/02/2020
PM..... 2001010..... STOCK..... 9.00 .....21/03/2020
PM..... 2001010..... STOCK..... 6.00..... 21/04/2020

The latest data (on 21/4/20) Amount = 6

I need to make my Table (A) display 6.

Hope some one can advise me.

Paul

[Frank_Hartmann · Accepted solution]

see attached

[paulyeo11]

Hi Sir

Thank you very much , it work fine now.
In case I need to change SET analysis . So that I can compare the stock amount compare to one year ago the stock amount it is up or down. May I know how to do it ?

Paul

[Frank_Hartmann · Accepted solution]

i would flag the max date per year in script and then using this information to find the values