Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Shahzad_Ahsan
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
shraddha_g
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
devarasu07
Honored Contributor II

Re: How to calculate average between columns in Qlik Sense

Data:

CrossTable(Type, Amount)

LOAD * INLINE [

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


Capture2.JPG




Capture.JPG

shraddha_g
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

)))

Shahzad_Ahsan
Contributor II

Re: How to calculate average between columns in Qlik Sense

Thanks Devarasu

You are right but I needed in qlik sense

You have answered in qlikview.

Its ok

Shahzad_Ahsan
Contributor II

Re: How to calculate average between columns in Qlik Sense

Thank You Shraddha

devarasu07
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

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

devarasu07
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


For check this article

The Crosstable Load


http://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/ScriptPrefixes/cross...

Community Browser