Do you want to sum the values for that date?
=FirstSortedValue( aggr(sum(Stock),Date), aggr( -Date,Date) )
or do you want the latest Stock value for that date?
Then you could introduce a timestamp / recno (whatever describes your 'latest' on that date) and use that instead of Date in above function.
Thanks for all the responses. Im almost there.
One thing, i want to total the:
=FirstSortedValue( Stock, -Date)
How can i achieve this? Sum(=FirstSortedValue( Stock, -Date)) does not work.
=FirstSortedValue( aggr(sum(Stock),Date), aggr( -Date,Date) ) only works for the latest values, but earlier dates get really big numbers
Yes I am. and =FirstSortedValue( Stock, -Date) works perfect to get the correct stock on a certain date, for each article.
but i want all the articles combined so I have a total. (so a month total of all stock)
Last one is same result as before btw. most recent date is correct (the total) but month earlier it starts to be different
U can try below scripts...
LOAD * INLINE [
Date, Stock, Mutations
20-06-2013, 70, 0
12-06-2013, 75, -5
06-06-2013, 76, -1
03-06-2013, 77, -1
12-04-2013, 12, 65
05-03-2013, 14, -2
15-02-2013, 16, -2
13-02-2013, 21, -5
30-01-2013, 27, -6
11-01-2013, 30, -3
Ltrim(Date(Date#(Date,'DD-MM-YYYY'),'DD/MM/YYYY')) as TRANDATE,
Resident RawData; DROP Table RawData;
Right(Date(Date#(TRANDATE,'DD/MM/YYYY'),'YYYY MMM'),3) as Month,