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

    Set Analysis with rowbased results

    Mark van der Meij

      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!