2 Replies Latest reply: May 7, 2012 2:04 PM by Sergey Pinchuk RSS

    set analysis - creating calculated dimention

      I'm trying to create a calculated dimention and having an issue with getting my set analysis to work. Any feedback is greatly apreciated.

       

      What i need to do is to display [Agency Name] for any time row in a orders table that has "(Begin+New) <> End" condition.

      Table look like this:

       

      Header 1Header 2Header 3Header 4Header 5
      Row IDAgency NameBeginNewEnd
      1Agency1527
      2Agency1718
      3Agency18311
      4Agency2123
      5Agency2325

      etc...

       

      This is what I have now and it works:

       

      =aggr(

      IF(Count( {$<[Begin] = {'<>[End]'} >} [Row ID]) > 0,[Agency Name])

      , [Agency Name])

       

      How can I do something like this (Begin + New)?

      =aggr(

      IF(Count( {$<([Begin]+[New]) = {'<>[End]'} >} [Row ID]) > 0,[Agency Name])

      , [Agency Name])

       

      Thank you!

        • set analysis - creating calculated dimention
          Oleg Troyansky

          Unfortunately, you can't use Set Analysis this way, for a few reasons. The main problem is that the Set Analysis condition is being validated once for the whole chart, not for each row.

           

          If your data is small enough, you can get by with the calculated dimension the way you created it. It will be slower than it shoudl be...

           

          The best way of solving this problem is creating a flag in the load script:

           

          LOAD

          ...

          IF (Begin + New <> End, 1, null()) as AgencyFlag

          ...

           

          Then your expression could simply look like this:

           

          sum({<AgencyFlag={1}>} Whatever)

           

          I'd also recommend implementing it in the Expression and not in the Calculated Dimension, because calculated dimensions are much heavier than expressions, in terms of performance.

           

          best,

           

          Oleg