Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Shahzad_Ahsan
Creator III
Creator III

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
Partner - Master III
Partner - Master III

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

)))

View solution in original post

7 Replies
devarasu07
Master II
Master II

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
Partner - Master III
Partner - Master III

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
Creator III
Creator III
Author

Thanks Devarasu

You are right but I needed in qlik sense

You have answered in qlikview.

Its ok

Shahzad_Ahsan
Creator III
Creator III
Author

Thank You Shraddha

devarasu07
Master II
Master II

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
Creator III
Creator III
Author

Hi

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

Data: 

CrossTable(Type, Amount) 

devarasu07
Master II
Master II

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