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
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
RutiTaumanRubin
Creator
Creator

Display values in numerator where exists in denominator - QlikSense

Hi

I create the following measure :

Avg(Aggr(
count({<ACTION={"OPENED"}>} Distinct ID)
/
count({<ACTION={"DELIVERED"}>} Distinct ID),ISSUE_ID))

 

I want to add condition to the numerator so it will display only the values that exists also in denominator (DELIVERED) for the same date that the user choose.

For Example:

IDACTIONDATE
111OPENED202001
111DELIVERED202001
222OPENED202002
222DELIVERED202001
333OPENED202002

 

If the user choose: DATE - 202001 it will display only the result for ID - 111

If the user choose: DATE - 202001 & 202002 it will display the result for IDs - 111 & 222 (it won't display the result for ID 333 because he doesn't have record for DELIVERED).

 

Could you please advise?

Thanks,

 

 

 

 

 

1 Solution

Accepted Solutions
RutiTaumanRubin
Creator
Creator
Author

The following calculation work for me:

 

Avg(Aggr(
count({<ACTION={"OPENED"}>*<ACTION={"DELIVERED"}>} Distinct ID)
/
count({<ACTION={"DELIVERED"}>} Distinct ID),ISSUE_ID))

View solution in original post

2 Replies
martinpohl
Partner
Partner

Hello,

 

use this expression:

count({<ACTION={"OPENED"}, ID = {"=count({<ACTION={'DELIVERED'}>} Distinct ID) > 0"}>} Distinct ID)

Regards

RutiTaumanRubin
Creator
Creator
Author

The following calculation work for me:

 

Avg(Aggr(
count({<ACTION={"OPENED"}>*<ACTION={"DELIVERED"}>} Distinct ID)
/
count({<ACTION={"DELIVERED"}>} Distinct ID),ISSUE_ID))