Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression with set analysis equivalent to Sum

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.

1 Solution

Accepted Solutions
sunny_talwar

May be this:

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

View solution in original post

3 Replies
sunny_talwar

May be this:

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

Not applicable
Author

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