Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

markvandermeij7
New Contributor

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!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

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

4 Replies

Re: Set Analysis with rowbased results

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

markvandermeij7
New Contributor

Re: Set Analysis with rowbased results

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:

stock_ts.png

ResultTable with missing fields

table.png

MVP
MVP

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

markvandermeij7
New Contributor

Re: Set Analysis with rowbased results

You sir, are a genius. Thank you!

Community Browser