Top Sales Week by Closer

lb1234

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.

MichelleH

@lb1234 How are you planning on visualizing this data? If you don't need to display the rank number then it would be simpler to graph count(distinct Opportunity Id) the data by the week of Project.SaleDate then sorting high to low by COUNT(distinct Opportunity Id).

lb1234

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(DISTINCT Opportunity 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.