4 Replies Latest reply: Jun 9, 2012 6:02 PM by Deepak Vadithala RSS

    Set Analysis with Variable

    Deepak Vadithala

      Dear All,

       

      Quick question on Set Analysis. I'm struggling to think and I need some help.

       

      I'm trying to use the Variable in Set Analysis which contains below expression :

       

      ONLY(ID)

       

      And my Set Analysis Expression is :

       

      =SUM({$<Part = {">$(=($(vTest)))"} >} MyCount)

       

      And this is returning 0 instead it should return same values as [Matching Output] column - (Please see attached example).

       

      I already achieved the final result in [Matching Ouput] field but when the users select the [Part] field then my numbers change and I don't them to change but there should be selection on the chart/table. That's reason I'm using the Set Analysis approach instead of using Above() and RangeSum() functions.

       

      I hope this makes sense.

       

      Thanks in advance.

       

      Cheers,

      DV

        • Re: Set Analysis with Variable
          Stefan Wühl

          Hi DV,

           

          it seems to me that you are trying to create a set expression that will evaluate the ID in the context of the current line, and I believe this will not work. The set expression will be evaluate once per chart (I think you can actually see the expression used when using <use expression> as label and then hover over the label in your chart column).

           

          There are methods to use a pick / match combination to simulate an evaluation per current dimension value (by creating in fact a set expression per line), check out the discussion thread here)

           

          Coming back to your original request, if I understood correctly, you can achieve what you want by embedding your working expression (with an appropriate set expression added) in an advanced aggregation, like

           

          =aggr(SUM({1}TOTAL MyCount) - RangeSum(Above(sum({1}MyCount),1,RowNo()) ),Part)

           

          See also attached,

          Stefan