topic Re: Showing Values, but only Latest Date in QlikView

Showing Values, but only Latest Date

Wed, 19 Jun 2013 09:53:10 GMT

Hello,

The following example illustrates my problem.

Date	StockMutation	Stock
10-04-2013	+10	20
10-04-2013	+10	30
10-04-2013	+10	40
14-04-2013	+40	80
14-04-2013	+40	120

For my graphs I want to display the stock for each day. In table form my intention looks as follows:

Date	Stock
10-04-2013	40
14-04-2013	120

How can I make an expression so only the latest value of a date is selected?

Re: Showing Values, but only Latest Date

Wed, 19 Jun 2013 10:49:41 GMT

TimestampFormat

required to get max time on perticular date.

=M/D/YYYY h:mm:ss

Re: Showing Values, but only Latest Date

Wed, 19 Jun 2013 11:02:21 GMT

Hey,

Can you explain me how i can use this? Cause i dont understand. Right now im trying the following, with not good results. (used in chart expression:)

sum(aggr(FirstSortedValue(Stock, -Date),Date, Stock))

Re: Showing Values, but only Latest Date

Wed, 19 Jun 2013 11:04:47 GMT

I am trying this, and it starts to look more like it. But still alot of values are not visible.

Re: Showing Values, but only Latest Date

er_mohit — Wed, 19 Jun 2013 11:29:17 GMT

Try this in script

AB:

LOAD date(Date#(DAte,'DD-MM-YYYY'),'DD-MM-YYYY')AS date,* INLINE [

DAte, StockMutation, Stock

10-04-2013, +10, 20

10-04-2013, +10, 30

10-04-2013, +10, 40

14-04-2013, +40, 80

14-04-2013, +40, 120

];

ABC:

LOAD max(Stock) as Lateststock,date

Resident AB

Group by date;

and in pivot table take date as dimension

and in expression write Lateststock

hope it helps you

Re: Showing Values, but only Latest Date

Wed, 19 Jun 2013 12:00:39 GMT

Thank you, i feel like im almost there.

The problem is, i dont want to sort on value, but really on date. so the latest value in the list. The value can also be lower than the first value.

Im sorry i didnt state this clear in the beginning

Re: Showing Values, but only Latest Date

er_mohit — Wed, 19 Jun 2013 12:05:26 GMT

then try this one

LOAD StockMutation,

max(Stock)as vmax,

FirstSortedValue(date(date),-Stock)as LTV

Group by StockMutation;

AB:

LOAD date(Date#(DAte,'DD-MM-YYYY'),'DD-MM-YYYY')AS date,* INLINE [

DAte, StockMutation, Stock

10-04-2013, +10, 20

10-04-2013, +10, 30

10-04-2013, +10, 40

14-04-2013, +40, 80

14-04-2013, +40, 120

];

Re: Showing Values, but only Latest Date

Wed, 19 Jun 2013 12:22:28 GMT

And how would the expression look like?

Re: Showing Values, but only Latest Date

er_mohit — Thu, 20 Jun 2013 05:25:32 GMT

Hiiii

For Expression side

try this one

Temp:

LOAD date(Date#(Dt,'DD-MM-YYYY'),'DD-MM-YYYY')AS DT,* INLINE [

Dt, SM, Stk

10-04-2013, +10, 20

10-04-2013, +10, 30

10-04-2013, +10, 40

14-04-2013, +40, 80

14-04-2013, +40, 120

];

take a dimension DT in pivot table and add expression

FirstSortedValue(aggr(max(Stk),SM),-aggr(DT,SM)) // For Stock

hope it helps you