Set analysis: greater than against total calls

[SAM81]

I have a bar chart summing the total amount of calls handled by a group of agents.  I want to exclude any agents with less than 20 calls.  The field to calculate against is called 'Calls' 

Easy enough - I'd just use set analysis: sum({<Calls = {">20"}>} Calls)

The problem I have is my data set is split by a dimension called Querydefinitionkey. In the example attached, although Pete is included in the chart, the summed value of his calls is incorrect.  It is only summing the top 3 rows where the Calls value is greater than 20.

It would show a value of 81 where i need it to show the total value of 89.

Any ideas?  

[David_Friend]

@SAM81 I don't work with app development myself, so I posted your question to chatgpt and it generated this answer, does this help:

Understanding the Problem

From your description, it seems that the set analysis {<Calls = {">20"}>} is filtering out data at a granular level, possibly within the Querydefinitionkey dimension, leading to an incorrect aggregation. To fix this, you need to ensure that the set analysis applies correctly to the data aggregated across the entire dimension.

Solution

1. Using Total in Set Analysis: You should use the Total keyword in your set analysis to aggregate over the entire dataset and then apply the condition to filter out agents with less than 20 calls.

   Here's how you can modify your set analysis expression:

   qlik

   Copy code

   Sum({<Calls = {">20"}>} Total Calls)

   This tells Qlik to first calculate the total calls for each dimension and then apply the condition to filter out those with less than 20 calls.

2. Pre-Aggregation: Alternatively, you can use a combination of an aggregation function and set analysis to ensure correct calculation.

   If you need to aggregate calls over each dimension and then exclude agents with less than 20 calls, you can use an intermediate calculation:

   qlik

   Copy code

   Sum( Aggr( If(Sum(Calls) >= 20, Sum(Calls)), Querydefinitionkey ) )

   Here’s what this does:

   • Aggr(If(Sum(Calls) >= 20, Sum(Calls)), Querydefinitionkey): This aggregates the calls for each Querydefinitionkey and includes only those with a sum greater than or equal to 20.
   • Sum(...): This then sums up the results of the aggregated values.

3. Using Alternate States: Another approach, if feasible, is to use alternate states to separate the filtering logic. Create an alternate state that filters out the agents with less than 20 calls, then use this alternate state to calculate the total.

   You can create an alternate state and apply the set analysis within that state to compute the required value.

Example

Assuming your field names and dimensions are correct, the adjusted formula to display the correct total should look like:

qlik

Copy code

Sum( Aggr( If(Sum(Calls) >= 20, Sum(Calls)), AgentName // Replace with the actual dimension used to group agents ) )

Replace AgentName with the actual dimension name used for grouping agents if different.

Summary

To fix the issue of incorrect totals due to the Querydefinitionkey dimension:

• Use Total in your set analysis to ensure aggregation works as expected.
• Consider using Aggr with conditional logic to aggregate and filter correctly.
• Alternatively, use alternate states if complex filtering is required.

Apply these methods to get the correct total value for your bar chart.

[SAM81]

Thanks for the quick reply.  I'd tried using total, but this gives each agent the total amount of calls in the entire data set - not just their own total value.  So they all have the exact same value.  

[Kushal_Chawda]

@SAM81  try below

sum({<EmployeeFullName = p({1< Calls = {">20"}>})>} Calls)