Calculating Volumes for Top Trade Partners

Rut — Wed, 12 Apr 2023 11:49:31 GMT

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
];

Re: Calculating Volumes for Top Trade Partners

Kushal_Chawda — Wed, 12 Apr 2023 20:39:41 GMT

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

Re: Calculating Volumes for Top Trade Partners

Rut — Thu, 13 Apr 2023 08:25:48 GMT

Simple and works perfectly. Thanks a lot @Kushal_Chawda!

topic Re: Calculating Volumes for Top Trade Partners in App Development

Calculating Volumes for Top Trade Partners

Re: Calculating Volumes for Top Trade Partners

Re: Calculating Volumes for Top Trade Partners