I want to build a card that shows the number of sales to date in a quarter, compared to last quarter and the quarter this time last year.
The data set has a "Close Date" in it, so I used that as the starting point. I can calculate the "Day of the Quarter" in Beast Mode and that lets me have COUNT(Sales This Quarter), COUNT(Sales Last Quarter), and COUNT(Sales This Time Last Year). Each of these 4 fields are built as a Beast Mode calculation.
I want to SUM the sales counts since the beginning of the quarter, so that each day of the quarter the column is higher than the one before (and hopefully reaching the goal). I'd have to be careful about using a Beast Mode calculation in another Beast Mode but we can work around that...
Thoughts? I don't know how to write this.
sum the count of sales deals since start of the quarter
SUM( COUNT (CASE
WHEN (day of quarter < current day of quarter) = `Amount: Sold` ) ) )
but it doesn't roll up effectively. And I need to do the same thing for the previous full quarters, so is there somethign like a WHILE function?
I use a beastmode function to do this: Case When (`EXCL Cert Detail-App Work Day`) > (`EXCL DateTable-MthBusDay`) then '2- As of Business Day or Later' else '1- As of Business Day or Earlier' end That splits all business days to current business day or earlier and after and allows for charts and totals and filtering depending on what you need. Has allowed me to do some of the same type of month to date or quarter to date comparisons.. Not exactly what you are trying to do but may work for you as well.
Dojo Community Member ** Please like responses by clicking on the thumbs up ** Please Accept / check the answer that solved your problem / answered your question.