Display values in numerator where exists in denominator - QlikSense

[RutiTaumanRubin]

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:

ID	ACTION	DATE
111	OPENED	202001
111	DELIVERED	202001
222	OPENED	202002
222	DELIVERED	202001
333	OPENED	202002

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,

[martinpohl]

Hello,

use this expression:

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

Regards

[RutiTaumanRubin · Accepted solution]

The following calculation work for me:

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