Top 10 for multiple Categories

Shevy

It is easy enough to build a card that only gives a Top 10 based on the criteria and the sort. I would like to get a report that lets me show all the sales people and their top 10 accounts without building a separate card for each sales person or without making the Manager have to filter to each sales person individually. So ideally it would do something like: Sales Person 1 Account 1 - Total sales Account 2 - Total sales . . Account 10 - Total Sales Sales Person 2 Account 1 - Total Sales . . Account 10 - Total Sales and so on..... Any thoughts?

NewsomSolutions

@AdamT I know this is super old but wanted to say thanks for posting that.  I had a use-case where I needed to do something similar and was able to do it in ETL's rank and windows vs MySQL.  But not sure if Rank&Window was an option in 2016, I can't remember.  But posting this in case someone else sees this old post and needs some help too.  Thanks again.

kshah008

Hi all,

 

Can anybody help @Shevy with their question?

Thanks!

AdamT

 

Hi @Shevy - Here is a post from one of our team members here at Domo on this very same topic. Below is the MySQL query he used to achieve this in a DataFlow.  

 

Selecting Top N Items From Each Group

 

Here is a generalized version of what I used to see the top 5 customers for each sales team based on revenue, in case it's a good starting point for someone else. The query assumes that the data has already been grouped, so In my case, I first wrote a transform to group the data based on the Sales Team and Customer, summing the Revenue for each record.  These fields have been generalized as Group_Field, Item_Field, and Measure_Field, respectively, in the code below.

 

 

Selecting from the Grouped Data

 

 

SELECT
    `Group_Field` -- The column that indicates the group of records for which we want to find the top n records
    , `Item_Field` -- The thing of which we want to find the top n for each group
    , `Measure_Field` -- The column we used to order the records so we know which are the top n
FROM
    (
        SELECT
            `Group_Field`
            , `Item_Field`
            , `Measure_Field`
            /* Gives an incremental row number if the group hasn't changed. If it has, it starts over at 1. */
            , (@row_num := CASE WHEN @Group_Variable = `Group_Field` THEN @row_num + 1 ELSE 1 END) as row_number
            /* Updates the value of @Group_Variable so it can be used in the next iteration */
            , (@Group_Variable := Group_Field) as `notUsed`
        FROM
            data_grouped_by_group_field_and_item_field -- Replace with name of table containing grouped values
            , (SELECT @row_num := 0) n -- Initializes @row_num variable to 0
            , (SELECT @Group_Variable := '') t -- Initializes @Group_Variable  to empty string
        ORDER BY
            `Group_Field`
            , `Measure_Field`
    ) sub_select
WHERE
    /* Use this to indicate how many Item records to return for each Group (currently set to 5) */
    sub_select.row_number <= 5


-- END OF QUERY

 

 

 

This query adds row numbers to records within each group in the subquery, allowing you to restrict the number of records coming back by setting the max row number you care about in the WHERE clause. 

 

kshah008

@Shevy, did AdamT's reply help you out? 

NewsomSolutions

@AdamT I know this is super old but wanted to say thanks for posting that.  I had a use-case where I needed to do something similar and was able to do it in ETL's rank and windows vs MySQL.  But not sure if Rank&Window was an option in 2016, I can't remember.  But posting this in case someone else sees this old post and needs some help too.  Thanks again.