My coworker and I have a question.
Essentially, we have a Division (column A) and a Class (Column B). Division is the parent (20 total divisions) and Class is the child (anywhere from 1-10 Classes under each Division).
How can we pull say the top 5 Divisions as well as the top 2 Classes in each Division? Is it possible to do this in the same query? Currently, we are just pulling a giant data set, putting it in excel, creating a Pivot Table and then organizing our report off that pivot. We were hoping there was a way to put it all into one query so no pivot table is needed.
Thanks and hoping for an answer to our question!
How do you define top 5 Divisions as well as the top 2 Classes?
Sounds like a task for an OLAP function.
Yes, if there is a table or view org containing division, klass and revenue, and you are ranking these things by revenue, then something like this seems accurate:
,sum(revenue) over( partition by division) as Drev
,sum(revenue) over( partition by division, "class" ) as Crev
qualify rank() over( order by Drev ) >= 5
and rank() over( partition by division order by Crev ) >= 2