How to calculate average between columns in Qlik Sense

[Shahzad_Ahsan]

Hi Everyone

I have table in excel like this

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

I want the result like this

Department	Average	Rank
D1	16.6	2
D2	10.6	2
D3	30	1
D4	18.4	2
D5	6.6	3

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

[devarasu07]

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

[shraddha_g · Accepted solution]

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]

Thanks Devarasu

You are right but I needed in qlik sense

You have answered in qlikview.

Its ok

[Shahzad_Ahsan]

Thank You Shraddha

[devarasu07]

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]

Hi

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

Data: 

CrossTable(Type, Amount) 

[devarasu07]

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