topic amount for latest date in QlikView

amount for latest date

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have below data for each Sector , I want to show the Order_Amount which belongs to last Order_Date for each Sector using Straight Table as shown below,

I am able to calculate Max_Order_date by using below formula

Max_Order_Date= Aggr(Max(Order_Date),Sector)

Data:

Sector	Order_Date	Order_Amount
CXHNN	12-Jun-2016	20
CXHNN	13-Jun-2016	70
CXHNN	14-Jun-2016	10
CXHNN	15-Jun-2016	60
CLD0P5	02-Jun-2016	10
CLD0P5	09-Jun-2016	20
CLD0P5	12-Jun-2016	90
CLD0P5	14-Jun-2016	40
CLD0P5	16-Jun-2016	50

Expected Results:

Sector	Max_Order_Date	Order_Amount
CXHNN	15-JUN-2016	60
CLD0P5	16-JUN-2016	50

Re: amount for latest date

swuehl — Fri, 17 Jun 2016 10:30:44 GMT

Dimension:

Sector

Expressions:

=Date(Max(Order_Date))

=FirstSortedValue( Order_Amount, -Order_Date)

edit:

or if you need to aggregate your amounts:

=FirstSortedValue( Aggr(Sum(Order_Amount),Sector,Order_Date), -Aggr(Order_Date,Sector,Order_Date) )

Re: amount for latest date

sunny_talwar — Fri, 17 Jun 2016 10:33:05 GMT

Just to add a small point, make sure that your Date are read properly. If those are not read as date, the solution might fall apart.

For reading the dates correctly read here:

Get the Dates Right

Why don’t my dates work?

Re: amount for latest date

swuehl — Fri, 17 Jun 2016 10:35:23 GMT

That's true and important, Sunny!

I assumed dates are correctly interpreted, since the OP is already able to use Max(Order_Date).

Re: amount for latest date

sunny_talwar — Fri, 17 Jun 2016 10:36:25 GMT

Did not suggest your response to be incorrect. Just wanted to point that date is an important component

Re: amount for latest date

Fri, 17 Jun 2016 11:33:48 GMT

Thanks all

Please suggest if below results display can be possible as well , sample data is same as mentioned in original post

Sector	Order_Date	Last_Order_Amount
CXHNN	12-Jun-16	60
CXHNN	13-Jun-16	60
CXHNN	14-Jun-16	60
CXHNN	15-Jun-16	60
CLD0P5	2-Jun-16	50
CLD0P5	9-Jun-16	50
CLD0P5	12-Jun-16	50
CLD0P5	14-Jun-16	50
CLD0P5	16-Jun-16	50

Re: amount for latest date

swuehl — Fri, 17 Jun 2016 12:04:24 GMT

Try

2 Dimensions:

Sector

Order_Date

1 Expression:

=FirstSortedValue( TOTAL<Sector> Order_Amount, -Order_Date)

Re: amount for latest date

Fri, 17 Jun 2016 12:32:36 GMT

Hi swuehl,

It is working like charm!

Re: amount for latest date

Fri, 24 Jun 2016 14:58:56 GMT

Hi swuehl,

Can you please help suggest how the expression showing expected results after including <Sector> after TOTAL

What I know is if <dimension> is mentioned after TOTAL then expression excludes the <dimension> while performing calculation so based on this above should ignore <Sector> and show the result as 50 based on <Last Order Date> i.e 16-Jun-2016

Re: amount for latest date

swuehl — Fri, 24 Jun 2016 15:45:36 GMT

No, the field listed in the TOTAL<Field> list is still considered as dimension, while any other dimension is ignored (Order_Date in your example with a chart with two dimensions Sector and Order_Date).