Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
markvandermeij7
Partner - Contributor
Partner - 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!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

4 Replies
Kushal_Chawda

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

markvandermeij7
Partner - Contributor
Partner - Contributor
Author

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

Miguel_Angel_Baeyens

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
Partner - Contributor
Partner - Contributor
Author

You sir, are a genius. Thank you!