How to use "OR" in the SET Analysis

[#1]

I have created an expression (which works perfectly):

= SUM ({<[User Comments 4WKS] = {"$(vUC4WKSCombined)"}> [User Comments 4WKS])

variable (vUC4WKSCombined) contains values from 0 to 100

but there are also NULL values for dimension [User Comments 4WKS]

Now my question is how can I change my expression so it can take care of both varialbe values from 0 to 100 and also NULL values

I need OR condition between two set modifiers, like dimesion  [User Comments 4WKS] can either take any values from 0 to 100 OR is NULL

= SUM ({<[User Comments 4WKS] = {"$(vUC4WKSCombined)"} or [User Comments 4WKS] = {"NULL"}> [User Comments 4WKS])

Can someone look into this and write the correct experssion?

Thanks,

TA

[Gysbert_Wassenaar]

See attached qvw.

[#3]

With reference to qvw you attached here.

Now both charts show the exact count only when >= 0 is selcted for all three page viewes options.

If any other values are selected, the count is till not the same.

For example. if you select:

PV1WK >=2

PV4WKS >3

PV52WKS >= 10

Then the SET expression does not give the correct answer 😞

[Gysbert_Wassenaar · Accepted solution]

I think your expressions in your IF chart didn't sum correctly. The sum of expression is different than the sum of the rows. In your original document try changing the operator for one of the three filters from >= to <. The totals of the IF chart then can't be calculated.

I've added a new version. I've changed the totalling of expression in the IF chart and I changed the SET expressions too. Both charts now return the same results as far as I can tell.

[#5]

Hi Gysbert,

Thank you very very much. I is really helpful. Although not 100% which I wanted but still very very close to that and I can change it now the way I want.

You are great 🙂

Thanks once again.

I will request you again if I need any help.

TA

[#6]

Hi Gysbert,

Just a quick question about the SET expression performance.

Do you think SET expression you have created in Set Analysis v3.qvw (attached in your last reply) will have better performace than IF expression?

The only reason of using SET instead of IF was to improve performance.

Pleaes advise.

Thanks,

TA

[Gysbert_Wassenaar]

My guess is that the set analysis expression will probably perform better. It is calculated only once per chart. The if statement is calculated for each row in turn. But I'm not sure. You'll have to try both and see if you can determine which performs better.

[#8]

Thanks for your reply.

SET should perform better than IF in normal cases. But my questions is aren't we using aggregation for each row in our SET expression as well? For example if we  compare there two expressions:

IF Expression:

=If(Sum ([User Comments 4WKS])$(vUC4WKSCombined) And

(Sum ([User Comments 26WKS])$(vUC26WKSCombined) And

(Sum ([User Comments 52WKS])$(vUC52WKSCombined)))

,Sum ([User Comments 4WKS]))

SET Expression:

= SUM ({<[AU ID] = {"=sum([User Comments 4WKS])$(vUC4WKSCombined)"}>  *

<[AU ID] = {"=sum([User Comments 26WKS])$(vUC26WKSCombined)"}> *

<[AU ID] = {"=sum([User Comments 52WKS])$(vUC52WKSCombined)"}>}[User Comments 4WKS])

They seem like pretty similar in terms of aggregation...aren't they?

Won't this part of the SET expression '{"=sum([User Comments 4WKS])$(vUC4WKSCombined)"}'

will calculate against each row as well?

Thanks,

TA

[Gysbert_Wassenaar]

They may look similar, but the set analysis expression is calculated only once. The set is always calculated once for a chart, never per row.

[#10]

OK. Thanks for your explanation. I really really appreciate your help and kind advise.

Happy New Year in advance :-))

[#11]

Hi Gysbert,

I hate to say but I have noticed that there is not a big difference in performance between IF and SET.

There whole purpose of this exerciese was to improve performance.

For some of my reports infact IF expressions generate repots faster than SET.

I don't know why 😞

Are we missing something in SET expression?

Is it possible to write same SET expression in a different way which can improve performance?

Do you have any advise for performance improvement?