Expression with set analysis equivalent to Sum

[#1]

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.

[sunny_talwar · Accepted solution]

May be this:

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

[#3]

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?

[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