Sales Revenue Ranking by dynamic date and limit pivot rows
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
Category  SubCategory  ReportingDate  Sales 
A  a  202011  200 
A  b  202011  300 
A  c  202011  100 
A  a  202010  100 
A  b  202010  200 
B  d  202011  150 
B  d  202010  350 
B  e  202011  100 
B  e  202010  400 
C  f  202011  150 
C  f  202010  200 
C  g  202011  300 
C  g  202010  100 
C  h  202011  210 
C  h  202010  250 
Here is the result:
If includes alltime, top 2 would be C and B; If the filter in reporting 202010, top 2 would be B and C; If the filter in reporting 202011, top 2 would be C and A.
Category  202010  202011  Sum 
C  550  660  1210 
B  750  250  1000 
A  300  600  900 
Thank you!
Comments

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 
Thanks for your help. I followed your instructions and everything is good before I limited rows. I had a 13month data in the pivot table in Domo with setting category as the xaxis and reporting date as the yaxis. After I limit rows to 30 (I have 45K 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?
Really appreciate your help!
0 
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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
 10.6K All Categories
 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 339 Workbench
 251 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 62 Kowaza
 295 仲間に相談
 649 ひらめき共有