Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Qlik Users, make your voice heard! Participate in Wisdom of Crowds® Market Study: Start Survey
cancel
Showing results for 
Search instead for 
Did you mean: 
RutiTaumanRubin
Creator
Creator

Ignore nulls from total

Hi Everyone,

I have create the following measure: 

(
Sum(If(Aggr(Avg(Aggr(
count({<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count({<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24')))>0.75,1,0))
/
count(Aggr(Avg(Aggr(
count( {<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count( {<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24'))))

)
/
Sum(total aggr(

Sum( If(Aggr(Avg(Aggr(
count( {<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count( {<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24')))>0.75,1,0))
/
count( Aggr(Avg(Aggr(
count( {<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count( {<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24'))))
,$(=GetObjectField(0,'CH24')))
)

 

I want the total to ignore nulls , I have tried to change tha calculatio to the following but it didn't work:

 

 

(
Sum(If(Aggr(Avg(Aggr(
count({<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count({<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24')))>0.75,1,0))
/
count(Aggr(Avg(Aggr(
count( {<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count( {<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24'))))

)
/
Sum(total {<$(=GetObjectField(0,'CH24')) = {'*'}>} aggr(

Sum( If(Aggr(Avg(Aggr(
count( {<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count( {<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24')))>0.75,1,0))
/
count( Aggr(Avg(Aggr(
count( {<ACTION_TYPE={"CLICKED"}>*<ACTION_TYPE={"DELIVERED"}>} DISTINCT ISSUE_ID)
/
count( {<ACTION_TYPE={"DELIVERED"}>} Distinct ISSUE_ID)
,EMP_KEY)),EMP_KEY,$(=GetObjectField(0,'CH24'))))
,$(=GetObjectField(0,'CH24')))
)

 

Could you please advise?

 

Thanks?

0 Replies