Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have data with trade volumes between 2 locations further divided into more categories in Qlik Sense:
I want to create a Table showing the Top 1 to 3 Trade Partners (To) for each Location (From) and the trade volume like this:
I have been able to get the Top X To Location using "=Only(Aggr(If(Rank(Sum(Volume))=X, To), From, To))" or "FirstSortedValue(To, -Aggr(Sum(Volume), From, To), X)".
However I struggle to create an expression to calculate the total Volumes for all From-To Pairs. All Set Analysis Expressions I create are evaluated on a global level and not per row.
I would appreciate if you could help me with this.
Thanks!
Here is the code used for this example:
Data:
NoConcatenate Load
F as From,
T as To,
C as Category,
V as Volume
Inline [
F, T, C, V
A, B, 1, 15
A, B, 2, 12
A, C, 1, 63
A, C, 2, 29
A, D, 1, 7
A, D, 2, 30
A, E, 1, 13
A, E, 2, 39
B, A, 1, 31
B, A, 2, 24
B, C, 1, 62
B, C, 2, 61
B, D, 1, 47
B, E, 1, 59
B, E, 2, 13
C, A, 1, 28
C, A, 2, 47
C, B, 1, 11
C, B, 2, 8
C, D, 1, 3
C, D, 2, 31
C, E, 1, 25
C, E, 2, 25
D, A, 1, 73
D, A, 2, 15
D, B, 1, 12
D, B, 2, 16
D, C, 1, 14
D, E, 2, 72
E, A, 1, 4
E, A, 2, 6
E, B, 1, 5
E, B, 2, 27
E, C, 1, 69
E, C, 2, 9
E, D, 1, 4
E, D, 2, 31
];
@Rut try below
Top 1 To:
=FirstSortedValue(distinct To , -aggr(Sum(Volume), From, To),1)
Top 1 Volume:
=FirstSortedValue(aggr(Sum(Volume),From, To) , -aggr(Sum(Volume), From, To),1)
Top 2 To:
=FirstSortedValue(distinct To , -aggr(Sum(Volume), From, To),2)
Top 2 Volume:
=FirstSortedValue(aggr(Sum(Volume),From, To) , -aggr(Sum(Volume), From, To),2)
...
@Rut try below
Top 1 To:
=FirstSortedValue(distinct To , -aggr(Sum(Volume), From, To),1)
Top 1 Volume:
=FirstSortedValue(aggr(Sum(Volume),From, To) , -aggr(Sum(Volume), From, To),1)
Top 2 To:
=FirstSortedValue(distinct To , -aggr(Sum(Volume), From, To),2)
Top 2 Volume:
=FirstSortedValue(aggr(Sum(Volume),From, To) , -aggr(Sum(Volume), From, To),2)
...
Simple and works perfectly. Thanks a lot @Kushal_Chawda!