Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

darrin_pilkingt
Contributor II

Sum If - If expression is true then 1 and sum those values not working

In my app I am capturing the Average QA scores for agents and identifying if they Exceed, Meet or Need Improvement.

The problem I am having is Totaling how many agents fall into each category. 

The expression to identify where they fall:

=if(avg(QAScore), < Only({<MetricID = {1} >} VariableValueBelow), 0,

if(avg(QAScore) >= Only({<MetricID = {1} >} VariableValueExceeds), 2,

if(isnull(avg(QAScore)), 0,

1)))

From the above I have broken out Below and Exceeds Figuring I could use the Count of Agents minus Below and Exceeds to find Meets:

Below:       if(avg(QAScore) < Only({<MetricID = {1} >} VariableValueBelow), 1)

Exceeds:   if(avg(QAScore) >= Only({<MetricID = {1} >} VariableValueExceeds), 1)

NameAvgBelowMeetsExceedsAgents
Dianna97.53%--11
Gardner93.57%--11
Michael88.54%---1
Guadalupe88.33%---1
Terry88.22%---1
Sandra85.40%---1
Kelley82.73%---1
Reginald81.16%---1
Pusey79.47%1--1

I am trying to get a final chart like this.  # of agents Below, Meets and Exceeds.

BelowMeetsExceeds
162

Thank you for you help.

1 Solution

Accepted Solutions
Not applicable

Re: Sum If - If expression is true then 1 and sum those values not working

try:

sum(aggr(

if(avg(QAScore), < Only({<MetricID = {1} >} VariableValueBelow), 0,

if(avg(QAScore) >= Only({<MetricID = {1} >} VariableValueExceeds), 2,

if(isnull(avg(QAScore)), 0,

1))), Name))

2 Replies
Not applicable

Re: Sum If - If expression is true then 1 and sum those values not working

try:

sum(aggr(

if(avg(QAScore), < Only({<MetricID = {1} >} VariableValueBelow), 0,

if(avg(QAScore) >= Only({<MetricID = {1} >} VariableValueExceeds), 2,

if(isnull(avg(QAScore)), 0,

1))), Name))

darrin_pilkingt
Contributor II

Re: Sum If - If expression is true then 1 and sum those values not working

Alfredo thanks,

That didn't actually work for what I am trying to do but that put me on the correct path.  Breaking out each individually did resolve this.


sum(aggr(if(avg(QAScore), < Only({<MetricID = {1} >} VariableValueBelow), 1), Name))

sum(aggr(if(avg(QAScore), >= Only({<MetricID = {1} >} VariableValueExceeds), 1), Name))

Thank you.

Community Browser