3 Replies Latest reply: Aug 31, 2016 10:46 PM by Sunny Talwar RSS

    Expression with set analysis equivalent to Sum

    Tony TP

      I have below expression working perfectly:

       

      =Sum({$<[Order Type]={'A'}>} if(Len(Trim([CC]))=0 OR Len(Trim([DD]))=0, 1, 0))

       

      and I would like to obtain the same result using Count in combination with set analysis, so:

       

      =Count({$<[Order Type]={'A'},[CC]={"=Len(Trim([CC]))=0"}>+<[DC]={"=Len(Trim([DC]))=0"} >}Order)

       

      But Count is not returning same result as Sum.

       

      The correct result is the one returned by Sum.

        • Re: Expression with set analysis equivalent to Sum
          Sunny Talwar

          May be this:

           

          =Count({$<[Order Type]={'A'}, Order = {"=Len(Trim([CC]))=0 or Len(Trim([DC]))=0"}>} Order)

            • Re: Expression with set analysis equivalent to Sum
              Tony TP

              It works. Could you explain me why {"=Len(Trim([CC]))=0 or Len(Trim([DC]))=0"} is assigned to Order?

              Set analysis syntax is sometimes a bit difficult to understand.

               

              In this case, Do you know which expression is more efficient in terms of performance, Sum or Count? or they are same?

                • Re: Expression with set analysis equivalent to Sum
                  Sunny Talwar

                  So this way of using set analysis is known as search string. The best way to see this is to create a straight table with Order as dimension and =Len(Trim([CC]))=0 or Len(Trim([DC]))=0 as expression. All those places where you see -1 showing for a particular order, those rows will be included in the expression. This can of search string can only be done if your straight table have only one dimension, if you somehow need to add 2 fields to get the right true, false, then this search string technique won't work unless you create a concatenated key of the two fields involved to get the right information in the straight table.

                   

                  I have not done a great job of explaining this, because it is somewhat difficult for me to explain this.

                   

                  To answer your second question, I think set analysis technique might be relatively better than if. But I have not done enough testing to confirm it

                   

                  Best,

                  Sunny