Windowing functions perform calculations over a dynamic range.
Example - Dynamically Display Your Top Five Reps
Use windowing to create a chart that dynamically displays your top-five reps for each country. The chart updates continuously as opportunities are won. The example uses windowing to calculate:
- Percentage contribution that each rep made to the total amount, partitioned by country
- Ranking of the rep’s contribution, partitioned by country
These calculations let us display the top-five reps in each country.
q = load "DTC_Opportunity_SAMPLE";
q = group q by ('Billing_Country', 'Account_Owner');
q = foreach q generate 'Billing_Country', 'Account_Owner',
-- sum(Amount) is the total amount for a single rep in the current country
-- sum(sum('Amount') is the total amount for ALL reps in the current country
-- sum(Amount) / sum(sum('Amount') calculates the percentage that each rep contributed
-- to the total amount in the current country
((sum('Amount')/sum(sum('Amount'))
-- [..] means "include all records in the partition"
-- "by Billing_Country" means partition, or group, by country
over ([..] partition by 'Billing_Country')) * 100) as 'Percent_AmountContribution',
-- rank the percent contribution and partition by the country
rank() over ([..] partition by ('Billing_Country') order by sum('Amount') desc ) as 'Rep_Rank';
-- filter to include only the top 5 reps
q = filter q by 'Rep_Rank' <=5;
The resulting graph shows the top-five reps in each country and displays each rep’s ranking.
