
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not equal in set analysts doesn't work
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The second option work for me - Thank you very much for your help!!
