Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
RutiTaumanRubin
Creator
Creator

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

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
jonathandienst
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
RutiTaumanRubin
Creator
Creator
Author

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