# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

## Count If Condition, Standard Deviation ,Average

Hi All,

I am facing a challenge to write expression involving count if, standard deviation and average.  I have the list of employees along with their test scores. I need to club the employees based on their score after normalization.

Employees Receiving more then [Average Score+ Standard Deviation of Score]  will be Top  Performers

Employees Receiving less then [Average Score-Standard Deviation of Score]  will be Bottom  Performers

Employees Receiving in the range of [Average Score-Standard Deviation of Score,Average Score+ Standard Deviation of Score ]  will be Average Performers.

I have attached the list of sample calculation done on excel.

Regards

Anurag Gupta

9 Replies
Honored Contributor II

## Re: Count If Condition, Standard Deviation ,Average

Is this?

Honored Contributor II

## Re: Count If Condition, Standard Deviation ,Average

attached the file qv

Honored Contributor II

## Re: Count If Condition, Standard Deviation ,Average

hi have a look at the attach example

Not applicable

## Re: Count If Condition, Standard Deviation ,Average

Hi Anurag,

Please see attached. I calculate the average and standard deviation on import, then classify each employee based on how their score compares to the classification system (top performer, bottom performer, etc.)

Hope that helps.

Matt

Not applicable

## Re: Count If Condition, Standard Deviation ,Average

Hi Enrique,

Thanks for your help. I am new to Qlik. Currently i am using Qliksense so not averse with Qlikview Scripting techniques.

Can you help me with QVF file ,if possible ?

Regards

Anurag Gupta

Not applicable

## Re: Count If Condition, Standard Deviation ,Average

Hi Mathew,

Thanks for your help. I am new to Qlik. Currently i am using Qliksense so not averse with Qlikview Scripting techniques.

Can you help me with QVF file ,if possible ?

Regards

Anurag Gupta

Not applicable

## Re: Count If Condition, Standard Deviation ,Average

Hi Liron,

Thanks for your help. I am new to Qlik. Currently i am using Qliksense so not averse with Qlikview Scripting techniques.

Can you help me with QVF file ,if possible ?

Regards

Anurag Gupta

Honored Contributor II

## Re: Count If Condition, Standard Deviation ,Average

sure thing

here is a qlik sense example

Not applicable

## Re: Count If Condition, Standard Deviation ,Average

Hi Anurag,

I'm not using qlik sense yet, but can try to walk you through it.

First, create an app, then open the script editor and add the following (my understanding is this will import fine in qlik sense):

Data:
Employee ID , Score
1, 100
2, 20
3, 30
4, 34
5, 36
6, 46
7, 69
8, 39
9, 87
10, 99
11, 34
12, 49
13, 89
14, 36
15, 100
16, 36
17, 46
18, 69
19, 39
20, 20
21, 21
22, 34
23, 45
24, 54
25, 67
26, 39
27, 39
28, 78
29, 99
30, 23
31, 49
32, 69
33, 96
34, 49
];

// Calculating average and standard deviation

Join
Avg(Score) as AverageScore,
Stdev(Score) as StdevScore
Resident Data;

// Classifying data based on performance
tmpData:
NoConcatenate
if(Score > AverageScore + StdevScore, 'Top Performer',
if(Score < AverageScore - StdevScore, 'Bottom Performer',
'Average Performer'))
as Class
Resident Data;

Drop Table Data;
RENAME Table tmpData to Data;

Then create a table and add Class as a dimension along with this calculated dimension which I called Range:

=if(Class = 'Top Performer', '>79', if(Class = 'Bottom Performer', '<28', if(Class = 'Average Performer', '>28 and < 79')))

Finally add the following expression to get the count per Class:

=count([Employee ID])