topic Set Analysis with rowbased results in QlikView

Set Analysis with rowbased results

markvandermeij7 — Mon, 31 Jul 2017 11:31:20 GMT

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 — Mon, 31 Jul 2017 12:19:45 GMT

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

markvandermeij7 — Mon, 31 Jul 2017 12:30:39 GMT

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 — Mon, 31 Jul 2017 14:17:32 GMT

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 — Mon, 31 Jul 2017 14:34:40 GMT

You sir, are a genius. Thank you!