Sales Revenue Ranking by dynamic date and limit pivot rows

user034162
user034162 Member
in Charting

Hello,

 

I am trying to get a pivot table card in Domo and it could show sales revenue by different categories and reporting date. More importantly, I hope to rank the categories by total sales revenue within a specific time and the card could only show the top N categories' records. Within a different period of time, the total sales revenue in different categories will vary, then the top N would change accordingly.

 

Here is an example. I want to choose the top 2 categories (based on total revenue) by sales revenue and date and set ReportingDate as a filter.

the dataset

CategorySubCategoryReportingDateSales
Aa2020-11200
Ab2020-11300
Ac2020-11100
Aa2020-10100
Ab2020-10200
Bd2020-11150
Bd2020-10350
Be2020-11100
Be2020-10400
Cf2020-11150
Cf2020-10200
Cg2020-11300
Cg2020-10100
Ch2020-11210
Ch2020-10250

 

Here is the result: 

If includes all-time, top 2 would be C and B; If the filter in reporting 2020-10, top 2 would be B and C; If the filter in reporting 2020-11, top 2 would be C and A.

Category2020-102020-11Sum
C5506601210
B7502501000
A300600900

 

Thank you!

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    here's a pretty extensive tutorial on window functions.  https://www.youtube.com/watch?v=eifSYZIcPzg

     

    you can't filter on a rank in window functions.  but you can set the data row limit to 2 or 5  or 10 rows and sort by sum(Amount) desc which is functionally the same thing.

     

    in your case if you have months on an axis, to get total for the year you'd write

    sum(sum(amount)) over (partition by category)

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user034162
    user034162 Member

    @jaeW_at_Onyx 

    Thanks for your help. I followed your instructions and everything is good before I limited rows. I had a 13-month data in the pivot table in Domo with setting category as the x-axis and reporting date as the y-axis. After I limit rows to 30 (I have 4-5K rows originally), the table only shows the data in the latest reporting date, the other data would disappear. I attached two images to show how it looks like before and after I limit rows to 30.

     

    Do you have a general idea about why it happened?

    https://ibb.co/xs3cwpV 

    https://ibb.co/HGTyC4N 

     

    Really appreciate your help!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    If you're using a pivot table with months on the axis, your data set is no longer 3 rows, it'll be around 3(categories) * 12(months) assuming you have data in each month... 

     

    in other words, it gets weird.  so don't use a pivot table if you want to limit rows that way.  instead build a beast mode for each month.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.

Categories