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.