Showing Values, but only Latest Date

[#1]

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?

[#2]

Hi

TimestampFormat

required to get max time on perticular date.

=M/D/YYYY h:mm:ss

[#3]

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))

[#4]

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

[er_mohit]

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

[#6]

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

[er_mohit]

ok

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

];

[#8]

And how would the expression look like?

[er_mohit · Accepted solution]

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