Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi
TimestampFormat
required to get max time on perticular date.
=M/D/YYYY h:mm:ss
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))
I am trying this, and it starts to look more like it. But still alot of values are not visible.
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
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
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
];
And how would the expression look like?
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