Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)))
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)))
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
)))
Thanks Devarasu
You are right but I needed in qlik sense
You have answered in qlikview.
Its ok
Thank You Shraddha
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
Hi
This is quite simple but could you explain what is the use of this line.
Data:
CrossTable(Type, Amount)
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