Top Sales Week by Closer
The objective is to get the closer's best week of sales without creating an ETL as we want this to be dynamic with the date range selected on the card. We have a list of sales (each have a unique opportunity Id) with project sale dates and closer names. I had thought this formula may work but I'm having issues:
RANK() over (PARTITION BY Closer.FullName
ORDER BY (COUNT(DISTINCT CASE WHEN Project.SaleDate
IS NOT NULL THEN Opportunity Id
END) OVER (PARTITION BY YEAR(Project.SaleDate
),WEEKOFYEAR(Project.SaleDate
))))
I'm trying to group the count of sales for each closer for each week and then rank them so that I can just pull in the count of sales for their best week through this secondary beast mode:
CASE WHEN RANK() over (PARTITION BY Closer.FullName
, YEAR(Project.SaleDate
), WEEK(Project.SaleDate
) order by COUNT(DISTINCT CASE WHEN Project.SaleDate
IS NOT NULL THEN Opportunity Id
END)) = 1
THEN COUNT(DISTINCT CASE WHEN Project.SaleDate
IS NOT NULL THEN Opportunity Id
END)
END
Any ideas? Again, while doing this in an ETL would be much easier, we want it to be dynamic with the date filter on the card.
Comments
-
I found a solution. I've used Row_Number to eliminate ties in the rank and I removed the week of year and year from the partition:
Row_Number() over (PARTITION BY
Closer.FullName
ORDER BY COUNT(DISTINCTOpportunity Id
) DESC, WEEKOFYEAR(Project.SaleDate
), YEAR(Project.SaleDate
))I then filtered for weeks with a row number of one on a table with a list of closers, sale weeks and number of sales.
1
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.8K Visualize
- 2.5K Charting
- 738 Beast Mode
- 56 App Studio
- 40 Variables
- 684 Automate
- 176 Apps
- 452 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 35 Predict
- 14 Jupyter Workspaces
- 21 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 123 Manage
- 120 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