25 Replies Latest reply: Jun 23, 2017 11:32 AM by Linda Pembroke RSS

    Set Analysis not pulling desired field

    Linda Pembroke

      Hi,

       

      I am currently using the following expression to pull a 'Rate' for an entire year regardless of selections and it is working: Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) . What I am wanting to do now is assign a 'Star' value based on where that rate falls on the threshold table (between PtileBeg and PtileEnd). However, I need the formula to point to the max Year and Month's thresholds as they vary each month.

       

      This is what I am trying to use but it isn't working:

      if(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS)>= only({<MaxYear,MaxMonth>}PtileBeg) and
      Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS)<= only({<MaxYear,MaxMonth>}PtileEnd),only({<MaxYear,MaxMonth>}Star))

       

      Any help is much appreciated.