topic Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35 in QlikView

Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

paulyeo11 — Sun, 26 Apr 2020 06:58:00 GMT

Hi All

I have below load script :-

GL_TABLE:
LOAD
'PM' as SOURCE,
[GL Code] as GL_CODE_,
If([GL Code]>=2001010 and [GL Code]<=2001010,'sTOCK_') as [sTOCK_],
Date( Date#([Posting Date], 'MM/DD/YYYY'), 'DD/MM/YYYY') as [date],
[C/D]*-1 as Amount,
[Cumulative Balance (LC)] as Amount_S
FROM
$(vRAWPath)$(vFile201)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Table Box list below :-

GL_CODE_ ----- Amount_S ----- date
2001010 ----- 933,824.35 ----- 24/04/2020
2001010 ----- 933,630.83 ----- 24/04/2020
2001010 ----- 933,622.48 ----- 24/04/2020
2001010 ----- 933,585.44 ----- 24/04/2020
2001010 ----- 933,577.09 ----- 24/04/2020

I need to display the latest date amount using FirstSortsValaue , in order to display 933,824.35 :-

Expression :-
=FirstSortedValue(Amount_S,-date)

But i get null value , not sure where go wrong ? Hope some one can advise me.

Paul

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

Anil_Babu_Samineni — Sun, 26 Apr 2020 07:06:50 GMT

Can you simply try?

Max(Amount_S)

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

paulyeo11 — Sun, 26 Apr 2020 07:11:42 GMT

Hi Sir

I just try your expression it return max value= 1,173,155 Which is not correct.

Paul

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

Anil_Babu_Samineni — Sun, 26 Apr 2020 07:21:54 GMT

FirstSortedValue works on aggregated data, But your's showing non-aggregate data? If you want Max date of the value. Try

Max({<date={$(=Date(Max(date),'DD/MM/YYYY'))}>} Amount_S)

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

paulyeo11 — Sun, 26 Apr 2020 07:31:48 GMT

Mr Anil

I just try your new expression, I still get null.

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

kumaranvr1984 — Sun, 26 Apr 2020 08:58:19 GMT

Hi,

Do you load any other field which contains time or row id something like that?

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

paulyeo11 — Sun, 26 Apr 2020 09:03:20 GMT

Hi Sir

it work fine when i create a sample raw data file , it dont have any issue. When i apply actual data to it got issue , because in between the row have other items beside stock. By the way i already use filter all stock item by GL_CODE.

Paul Yeo

Re: Case 002 How to display the latest date amount using FirstSortsValaue = 933,824.35

Saravanan_Desingh — Fri, 15 May 2020 20:39:11 GMT

This might be failing because, you have repeated date values. May be try like:

=FirstSortedValue(Amount_S,-date+Amount_S)

This will make the date Field unique.