Not equal in set analysts doesn't work

[RutiTaumanRubin]

Hi All,

I create the following measure andit's work:

Avg(Aggr(
count({<ACTION={"OPENED"},IND_849={'1'}>*<ACTION_TYPE={"DELIVERED"},IND_849={'1'}>} Distinct ID)
/
count({<ACTION={"DELIVERED"},IND_849={'1'}>} Distinct ID)
,ISSUE_ID,$(=GetObjectField(0,'CH24'))))

When I try to change the measure so fied IND_849 not equal to 1 , I don't recived any data (this field include 1\null data).

Avg(Aggr(
count({<ACTION={"OPENED"},IND_849-={'1'}>*<ACTION_TYPE={"DELIVERED"},IND_849-={'1'}>} Distinct ID)
/
count({<ACTION={"DELIVERED"},IND_849-={'1'}>} Distinct ID)
,ISSUE_ID,$(=GetObjectField(0,'CH24'))))

Could you please advise?

Thanks

[jonathandienst · Accepted solution]

Set analysis (like user selections) cannot select null values. If you have access go the load script, you could replace the nulls with (say) 0. Then the problem is simple.

If not, you could break out of set analysis for that bit:

Avg(Aggr(
count({<ACTION={"OPENED"}>*<ACTION_TYPE={"DELIVERED"}>} Distinct If(IsNull(IND_849), ID))
/
count({<ACTION={"DELIVERED"},IND_849-={'1'}>} Distinct If(IsNull(IND_849), ID))
,ISSUE_ID,$(=GetObjectField(0,'CH24'))))

This should work OK for small to medium sized data sets, but possibly not well for large data sets. The Avg(if()) logic is inherently less efficient than pure set analysis. Otherwise, you could:

Avg(Aggr(
(count({<ACTION={"OPENED"}>*<ACTION_TYPE={"DELIVERED"}>} Distinct ID) -
count({<ACTION={"OPENED"},IND_849={'1'}>*<ACTION_TYPE={"DELIVERED"},IND_849={'1'}>} Distinct ID))
/
(count({<ACTION={"DELIVERED"}>} Distinct ID) -
count({<ACTION={"DELIVERED"},IND_849={'1'}>} Distinct ID))
,ISSUE_ID,$(=GetObjectField(0,'CH24'))))

There might be other ways, but I need a sample to test that, and I am not using QS at the moment.

[RutiTaumanRubin]

The second option work for me - Thank you very much for your help!!