Using a field within E() set modifier and the aggregation count.

[Lobby]

Hi all, thank you for your support in advance! Some background about my data (unfortunately cannot share) :

I have a data model of work cases, the fields include worker_id, case ID, case type, start date, and completion date. If the case hasn't been started or completed, those fields will be null values. If a case has been open for a month and isn't completed it is 'overdue'. I am not the app owner so limited control.

My objective is to create a dashboard that has a table listing all cases that do not have a completion date and a KPI that counts the number of cases and the number of workers. I have used set analysis and the functions P() and E() to successfully create the API. Specifically:

Count({<
case_type={'X','Y'}
,case_id=E({1<completion_date={"*"}>})
>}
Distinct case_id)

The above code works to count the number of cases and I replace the case_id at the end with worker_id to get the count of workers. However, I noticed that when I select a single case, the case-count KPI reflects the total count of cases still where as the worker-count KPI changes to reflect the manual selection I have made. I am trying to get both to reflect manual selections made. I tried removing the 1 in my E() function which made no difference and I have tried using a different field to equate to my E() - i.e case_type=E({1<completion_date={"*"}>}) - and I get a zero count instead.

Can anyone maybe explain why this isn't working - am I using E() incorrectly? -  and suggests any alternatives?

[samuel898]

---

@Lobby wrote:

Hi all, thank you for your support in advance! Some background about my data (unfortunately cannot share) :

I have a data model of work cases, the fields include worker_id, case ID, case type, start date, and completion date. If the case hasn't been started or completed, those fields will be null values. If a case has been open for a month and isn't completed it is 'overdue'. I am not the app owner so limited control. Official Site

My objective is to create a dashboard that has a table listing all cases that do not have a completion date and a KPI that counts the number of cases and the number of workers. I have used set analysis and the functions P() and E() to successfully create the API. Specifically:

Count({<
case_type={'X','Y'}
,case_id=E({1<completion_date={"*"}>})
>}
Distinct case_id)

The above code works to count the number of cases and I replace the case_id at the end with worker_id to get the count of workers. However, I noticed that when I select a single case, the case-count KPI reflects the total count of cases still where as the worker-count KPI changes to reflect the manual selection I have made. I am trying to get both to reflect manual selections made. I tried removing the 1 in my E() function which made no difference and I have tried using a different field to equate to my E() - i.e case_type=E({1<completion_date={"*"}>}) - and I get a zero count instead.

Can anyone maybe explain why this isn't working - am I using E() incorrectly? -  and suggests any alternatives?

---

Hello,

It’s great that you’re using set analysis to create your dashboard. Let’s address the issue you’re facing with the KPIs not reflecting manual selections as expected.

E() Function:
The E() function in set analysis is used to exclude specific values from a field.
In your case, you’re excluding cases with a completion date (i.e., {1<completion_date={"*"}>}).
However, this exclusion applies to the entire expression, affecting both case count and worker count.
Alternative Approach:
To achieve your goal, consider using separate expressions for case count and worker count.
Instead of using E(), create two separate expressions—one for cases and one for workers.
Example Expressions:
Case Count:

Count({<case_type={'X','Y'}, completion_date={"*"}>} Distinct case_id)

Worker Count:
Count({<case_type={'X','Y'}, completion_date={"*"}>} Distinct worker_id)

Explanation:
By including completion_date={"*"}, you ensure that both case count and worker count consider only cases without a completion date.
This way, manual selections will affect each count independently.
Remember to adapt the expressions to your specific field names and requirements. Hopefully, this approach will give you the desired results!

Best regards,
samuel898

[Lobby]

Hi Samuel,

Thank you for the reply! I fear I was clear in my post, allow me to try and correct that.

I meant that I have two KPIs, one that counts workers and one that counts cases. Rereading my post, I see how that reads as if I have one that does both, what I get for posting at the end of the work day!

That being said, I appreciated your suggested set analysis. I replaced the E() part with just "completion_date-={"*"}" (minus sign for not equals to in order to exclude anything with a completion data) but this just now returns zero?

My confusion stems from the fact that this KPI doesn't respond when a case ID is selected:

Count({<
case_type={'X','Y'}
,case_id=E({1<completion_date={"*"}>})
>}
Distinct case_id)

But this one does:

Count({<
case_type={'X','Y'}
,case_id=E({1<completion_date={"*"}>})
>}
Distinct worker_id)

My suspicion is because the case_id is used in the E() term where as worker_id is not. As I said, trying to use a different field in the E() term returns zero so I'm a bit stuck. I would appreciate any insight into this!

[lennart_mo · Accepted solution]

Hi @Lobby,

I'm not sure if it'll work but have you tried using set operators? Using * as a set operator should give you an intersection of two datasets, so my suggestion would be:

Count({<
case_type={'X','Y'}
,case_id=E({1<completion_date={"*"}>
*$<case_id>})
>}
Distinct case_id)

If i understand the documentation correctly, the first set expression gives you all case_ids without a completion date and the second should give you all case_ids matching the current selection, so i guess the intersection should return your required dataset.

Let me know, if this worked for you!

EDIT:

Apparently you can just use '*=', according to official Qlik documentation this already returns the intersection of all selected values and the set expression.

So your expression should work like this:

Count({<
case_type={'X','Y'}
,case_id*=E({1<completion_date={"*"}>})
>}
Distinct case_id)

[Lobby]

Thank you so much! That fixed it immediately, I really appreciate your help!