Set Analysis with rowbased results

[markvandermeij7]

Hi,

I'm having some trouble setting up a correct set analysis query.

I'm working with stock amounts for certain articles.

Selected are, for example, the year 2017 and the periode Week 12-Week 20

ARTICLE CD	ARTICLE	STOCKSTART	STOCK MUTATIONS	STOCK END
AA001	ARTICLE AA	<broken> <nothing>	600	<a value>
BB001	ARTICLE BB	<broken> <nothing>	-500	<broken> <nothing>
CC001	ARTICLE CC	<a value>	50000	<broken> <nothing>

Each stock transaction has a timestamp.

So I would want the minimum timestamp for stockstart, and maximum timestamp for stock end.

This only seems to work on the entire table. Not on each specific row.

I want a stock result on each row.

Things that don't work:

SUM({<[stock_timestamp]={$(=min([stock_timestamp]))}>} Stock.Startvalue)

SUM({<[stock_timestamp]={$(=max([stock_timestamp]))}>} Stock.Endvalue)

I've also tried :

SUM(AGGR(SUM({<[stock_timestamp]={$(=min([stock_timestamp]))}>} Stock.Startvalue),[ARTICLE CD],[ARTICLE]))

SUM(AGGR(SUM({<[stock_timestamp]={$(=min([stock_timestamp]))}>} Stock.Startvalue),[ARTICLE],[ARTICLE CD]))

SUM(AGGR(SUM({<[stock_timestamp]={$(=min([stock_timestamp]))}>} Stock.Startvalue),[ARTICLE]))

Anyone have any idea how I can get this to work?

Thanks!

[Kushal_Chawda]

what is the format of [stock_timestamp] field, also check if it is in number format or not

[markvandermeij7]

Hello Kushal,

The format is deffinitely numeric. 14 digits.

YYMMDDmmhhssmm, made numeric.

As said, the results only pop up once in the table, and not for each row.

Sourcedata:

ResultTable with missing fields

[Miguel_Angel_Baeyens · Accepted solution]

I would try

• FirstSortedValue(TIMESTAMP, STOCKSTART) for the minimum value and
• FirstSortedValue(TIMESTAMP, -STOCKSTART) (see the "-" in front of the second parameter) for the maximum value

And elaborate from there, if needed

[markvandermeij7]

You sir, are a genius. Thank you!