Dynamic Report with Ranking
Hi, I wanted to create a report that shows sales rank of each product by Store. However, because there are more than one transaction for each model, there are multiple rows for the same product in the dataset. As a result, I have multiple ranking numbers for the same model, when I use the rank & window feature in ETL. Is there a way that I can group the products so that the report sums all transactions of a product model by store and ranks the models by the sum?
Comments
-
Hi @user069636
Try using a Group By tile before your Rank & Window tile to group by the product and store and use the SUM aggregation on your necessary fields
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@GrantSmith It looks like the Group By function drops all other columns not being used. Is there a way to still have the unused columns after grouping by?
0 -
the window function isn't super great for these percent of total type calcs. the design pattern @GrantSmith is recommending is calculate your categegory totals with a group by, rank the subtotal, then JOIN the results back to your main dataset.
This is potentially unweildy so you could implement this design in a dataset view, beta feature, talk to your CSM, JOINED back to the source data.
alternatively... just do it in analyzer. rank() over (partition by store order by sum(amount)). if you don't have the feature switch enabled in your instance talk to your CSM.
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"2 -
@jaeW_at_Onyx is there a way define the order by to descending? Appears too default to ascending?
thank you!
0 -
never mind! Found it!
rank() over (order by sum(`amount`)desc)
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 738 Beast Mode
- 57 App Studio
- 40 Variables
- 685 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive