How to make April 2020 display 6 ?

[paulyeo11]

Hi All

Below is my raw data , i want to plot the bar chart , for April 2020 month , i want it display the latest data amount. that is 6. i try to use sum(Amount) i get 15 which is wrong. i want to display 6.

Dimension = YearMonth

Expression = sum(Amount)

which is wrong.

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 .....20/04/2020
PM..... 2001010..... STOCK..... 6.00..... 21/04/2020

Hope some one can advise me.

Paul

[tresesco]

What if you use expression like :

FirstSortedValue(Amount)

[paulyeo11]

Hi Sir

one more forum user offer me :-

FirstSortedValue(Amount,date)

I notice that with date is much better , I can change my dimension to YearMonth it will display this year and last year stock amount.

For your with out date , how should I use it ? Or use at where ?

Paul

[Saravanan_Desingh · Accepted solution]

Are you looking something like this? I scrambled your data.

tab1:
LOAD PurgeChar(F1,'.') As F1;
LOAD * INLINE [
	F1
    PM1..... 2001010..... STOCK..... 18.00 ....21/09/2019
    PM1..... 2001010..... STOCK..... 14.00 ....21/10/2019
    PM2..... 2001010..... STOCK..... 19.00 ....21/11/2019
    PM2..... 2001010..... STOCK..... 22.00 ....21/12/2019
    PM2..... 2001010..... STOCK..... 14.00 ....21/01/2020
    PM3..... 2001010..... STOCK..... 12.00 ....21/02/2020
    PM4..... 2001010..... STOCK..... 9.00 ....20/04/2020
    PM4..... 2001010..... STOCK..... 6.00 ....21/04/2020
];

tab2:
LOAD *
From_Field(tab1,F1)
(delimiter is ' ', no labels);

tab3:
LOAD @1, FirstSortedValue(@4/100,-Date#(@5,'DD/MM/YYYY')) As Value
Resident tab2
Group By @1
;

Drop Table tab1, tab2;

[paulyeo11]

Hi Sir

Thank you for your sharing . Now I understand how to apply Tressco expression in load script.

Also you are very good , you know how to take my raw data and read into Qlik View . In future how should I post my raw data on the body page ? So that raw data not so messy.

[paulyeo11]

Hi All

After sample raw data work fine. I have try to implement your solution into my actual raw data.

FirstSortedValue(Amount,-date)

Table A Return null value.

Hope some one can advise me where go wrong ?

I Hope it can return 933,487 ( Refer to Table 2 )

Enclosed my QVW.

Paul