9 Replies Latest reply: Jun 28, 2016 1:12 PM by Sunny Talwar RSS

    Nested Sets In Set Analysis

    Joel Ferringer

      I have what seems to be a simple problem, where I would like to return a value from a set analysis expression that corresponds to a max date, where the value column is not null.

       

      Consider the data:

      DATE
      DIMENSIONVALUENULLFLAG

      1/1/2016

      A

      .956

      0

      2/1/2016A.9670
      3/1/2016A.9480
      1/1/2016B.9930
      2/1/2016B1
      3/1/2016B1
      1/1/2016C.9970
      2/1/2016C.9590
      3/1/2016C.9980
      1/1/2016A.9990
      2/1/2016A.9890
      3/1/2016A.9190
      1/1/2016B.8990
      2/1/2016B1
      3/1/2016B1
      1/1/2016C.9020
      2/1/2016C.9490
      3/1/2016C.9870

       

      I am trying to create an expression that will return the max value for the most recent non-null date.

      I've tried just about everything.

      I can get the correct date to resolve correctly with this expression: MAX({<DATE, NULLFLAG={0}>}DATE).

      I then try to nest that inside my next set to get the max value using: MAX({$<Date={'$(=MAX({<NULLFLAG={0}>}Date))'}>}VALUE).

      But the nested set for the date does not get calculated on a row by row basis. It always returns a max date of Mar-16 for each row, ignoring the DIMENSION. So my results look like this:

      1.PNG

      The value for DIMENSION B should be the max from 1/1/2016, which would be row 4, .993.

      I also tried using the AGGR() function, but I got the same results.

      Is my syntax incorrect, or do I need to change my approach?

      Thanks!