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 :




      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.




        • 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,