Add Rank column to table at specific level of detail

[NattiNatey]

I have a data source table with dimensions called Agency and Advertiser, and a measure called Revenue. I am trying to add a Rank column that will show the rank of the Revenue at the Advertiser level of aggregation only. I tried the below expression but it did not give me the result I need.

RANK(TOTAL
AGGR(SUM([Revenue]), [Advertiser]),1)

Here is a specific example in SQL with sample data that gives me the desired result for reference:

WITH temp_sales_table AS (
SELECT * FROM VALUES
('Agency A', 'Advertiser 1', 120000.00),
('Agency A', 'Advertiser 2', 95000.00),
('Agency A', 'Advertiser 3', 87000.00),
('Agency B', 'Advertiser 1', 110000.00),
('Agency B', 'Advertiser 4', 76000.00),
('Agency B', 'Advertiser 5', 99000.00),
('Agency C', 'Advertiser 2', 88000.00),
('Agency C', 'Advertiser 6', 67000.00),
('Agency C', 'Advertiser 7', 72000.00),
('Agency C', 'Advertiser 1', 105000.00)
AS temp_table(Agency, Advertiser, Revenue)
),

aggTable AS (
SELECT Advertiser, SUM(Revenue) AS NetBookedRev
FROM temp_sales_table
GROUP BY Advertiser
),

rankTable AS (
SELECT
Advertiser,
NetBookedRev,
RANK() OVER (ORDER BY NetBookedRev DESC) AS Rank
FROM aggTable
),

advAggTable AS (
SELECT Agency, Advertiser, SUM(Revenue) AS Revenue
FROM temp_sales_table
GROUP BY Agency, Advertiser
)

SELECT advAggTable.Agency,
advAggTable.Advertiser,
advAggTable.Revenue,
rankTable.Rank
FROM advAggTable
LEFT JOIN rankTable
ON rankTable.Advertiser = advAggTable.Advertiser
ORDER BY Agency, Rank

This results in the following table.

Agency	Advertiser	Revenue	Rank
Agency A	Advertiser 1	120000	1
Agency A	Advertiser 2	95000	2
Agency A	Advertiser 3	87000	4
Agency B	Advertiser 1	110000	1
Agency B	Advertiser 5	99000	3
Agency B	Advertiser 4	76000	5
Agency C	Advertiser 1	105000	1
Agency C	Advertiser 2	88000	2
Agency C	Advertiser 7	72000	6
Agency C	Advertiser 6	67000	7

Here the Revenue is shown at the Agency, Advertiser level of detail, and the Rank is showing the Rank of Revenue at the Advertiser level of detail.

Is there a way to do this in Qlik? I need to do on the front end using expressions. Thanks!

[hanna_choi · Accepted solution]

Hello

How about this ways?

*** Data Load Script

***** Visualization

expression : aggr(nodistinct Rank(Sum(Revenue)), Advertiser)

[NattiNatey]

This worked thanks so much @hanna_choi !