topic Re: Pick the latest value in QlikView

Pick the latest value

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

EX: My table look like below

ID Month Amt

1 Jan 500

1 Feb 600

1 Mar 400

1 Apr 300

2 Jan 500

2 Feb 600

2 Mar 400

Here ID 1 should pick Apr AMT and ID 2 should pic Mar Amt.

Sum(Amt) for ID 1 should be 300 and Sum(Amt) for ID 2 should be 400.Can we do this ?

Thanks much.

Re: Pick the latest value

sunny_talwar — Mon, 30 Jan 2017 22:49:35 GMT

May be like this:

Dimension

Expression

FirstSortedValue(Aggr(Sum(Amt), ID, Month), -Aggr(Month, ID, Month))

Re: Pick the latest value

trdandamudi — Tue, 31 Jan 2017 00:15:52 GMT

Sunny,

I tried your expression and I am getting no result... Am I missing something here ?

Re: Pick the latest value

trdandamudi — Tue, 31 Jan 2017 00:26:49 GMT

May be you can give a try as below:

Data:

Load

RowNo()as RowID,*

Inline [

ID, Month, Amt

1, Jan, 500

1, Feb, 600

1, Mar, 400

1, Apr, 300

2, Jan, 500

2, Feb, 600

2, Mar, 400

];

Dimension:

RowID

Expression:

FirstSortedValue(Aggr(Sum(Amt), RowID, Month), -Aggr(RowID,RowID,Month))

Note: If you also want to show the month name then you can also use the below expression to display the month:

Expression:

FirstSortedValue(Aggr(Month, RowID, Month), -Aggr(RowID,RowID,Month)) <--- This will display the month like Mar or April

Re: Pick the latest value

sunny_talwar — Tue, 31 Jan 2017 02:27:29 GMT

What wrong trdandamudi‌, seems to be working (once Month is a dual field)

Script

Table:

LOAD ID,

Month(Date#(Month, 'MMM')) as Month,

Amt

Inline [

ID, Month, Amt

1, Jan, 500

1, Feb, 600

1, Mar, 400

1, Apr, 300

2, Jan, 500

2, Feb, 600

2, Mar, 400

];

For Month, I would just do Max(Month) instead of the complicated FirstSortedValue() Expression

Re: Pick the latest value

trdandamudi — Tue, 31 Jan 2017 02:41:09 GMT

Sunny,

Got it... I missed the line Month(Date#(Month, 'MMM')) as Month completely...

Thanks...

Re: Pick the latest value

sunny_talwar — Tue, 31 Jan 2017 02:43:16 GMT

Well ya... if it is text, then FirstSortedValue() will never work . I assumed that Month is dual and that's why I proposed the expression.