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 | 2020-11 | 200 |
A | b | 2020-11 | 300 |
A | c | 2020-11 | 100 |
A | a | 2020-10 | 100 |
A | b | 2020-10 | 200 |
B | d | 2020-11 | 150 |
B | d | 2020-10 | 350 |
B | e | 2020-11 | 100 |
B | e | 2020-10 | 400 |
C | f | 2020-11 | 150 |
C | f | 2020-10 | 200 |
C | g | 2020-11 | 300 |
C | g | 2020-10 | 100 |
C | h | 2020-11 | 210 |
C | h | 2020-10 | 250 |
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.
Category | 2020-10 | 2020-11 | Sum |
C | 550 | 660 | 1210 |
B | 750 | 250 | 1000 |
A | 300 | 600 | 900 |
Thank you!