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
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 467 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 193 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive