4 Replies Latest reply: Jul 31, 2017 10:34 AM by Mark van der Meij

# Set Analysis with rowbased results

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 CDARTICLESTOCKSTARTSTOCK MUTATIONSSTOCK END
AA001ARTICLE AA<broken> <nothing>600<a value>
BB001ARTICLE BB<broken> <nothing>-500<broken> <nothing>
CC001ARTICLE 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!

• ###### Re: Set Analysis with rowbased results

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

• ###### Re: Set Analysis with rowbased results

Hello Kushal,

The format is deffinitely numeric. 14 digits.

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

Sourcedata:

ResultTable with missing fields

• ###### Re: Set Analysis with rowbased results

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

• ###### Re: Set Analysis with rowbased results

You sir, are a genius. Thank you!