# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor II

## 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

1 Solution

Accepted Solutions
Honored Contributor III

## 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

)))

7 Replies
Honored Contributor II

## 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)))

Honored Contributor III

## 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

)))

Contributor II

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

Thanks Devarasu

You are right but I needed in qlik sense

Its ok

Contributor II

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

Honored Contributor II

## 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

Contributor II

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

Hi

This is quite simple but could you explain what is the use of this line.

Data:

CrossTable(Type, Amount)

Honored Contributor II

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

Hi,

It's one type of load in qlik. & It's used to do transformation Columns into row conversion or row into column

I.e: The crosstable prefix is used to turn a cross table into a straight table