7 Replies Latest reply: Nov 21, 2017 6:14 AM by Devarasu R

# How to calculate average between columns in Qlik Sense

Hi Everyone

I have table in excel like this

DepartmentABCDE
D1269633
D2698228
D34785963
D42548586
D535889

I want the result like this

DepartmentAverageRank
D116.62
D210.62
D3301
D418.42
D56.63

Please answer how to calculate Average & Rank. See the below hint.

Average= A+B+C+D+E/5

Rank= If Average > 20 then Rank=1, If Average Between 10 and 20 then Rank=2, If Average < 10 then Rank=1

• ###### Re: How to calculate average between columns in Qlik Sense
```Data:
```
```CrossTable(Type, Amount)
Department, A, B, C, D, E
D1, 2, 6, 9, 63, 3
D2, 6, 9, 8, 22, 8
D3, 47, 85, 9, 6, 3
D4, 25, 48, 5, 8, 6
D5, 3, 5, 8, 8, 9
];
```

in you exp:

avg:

Avg( Aggr( Sum(Amount), Department, Type ) )

Rank Exp:

if( Avg( Aggr( Sum(Amount), Department, Type ) )>20,1,

if( Avg( Aggr( Sum(Amount), Department, Type ) )<=10,3,

if( Avg( Aggr( Sum(Amount), Department, Type ) )>=10 and  Avg( Aggr( Sum(Amount), Department, Type ) )<=20,2)))

• ###### Re: How to calculate average between columns in Qlik Sense

Thanks Devarasu

You are right but I needed in qlik sense

Its ok

• ###### Re: How to calculate average between columns in Qlik Sense

Hi,

instead of hard-coding your dimension value use the agg function. refer to the qliksense attached file.

same expression should work for both qlikview/sense.

Thanks

Deva

• ###### Re: How to calculate average between columns in Qlik Sense

Try:

For Average:

(Sum(A) +Sum(B) + Sum(C) + Sum(D) +Sum(E))/5

For Ranking,

if((Sum(A) +Sum(B) + Sum(C) + Sum(D) +Sum(E))/5 >20 , 1,

if((Sum(A) +Sum(B) + Sum(C) + Sum(D) +Sum(E))/5 >10 and Sum(A) +Sum(B) + Sum(C) + Sum(D) +Sum(E))/5 <20 , 2,

if((Sum(A) +Sum(B) + Sum(C) + Sum(D) +Sum(E))/5 <10 , 1

)))