Help with Beast Mode field please!

reichner015

Hello Community,

I have a beast mode field that is working great when I am using a single number chart looking at the total for the current quarter we are in. The problem I need help with is, what can I update or what am I missing from the below Beast Mode field that will not allow me to show each month in the current quarter with the totals for each month. Right now how it is behaving is when I use a bar chart all of the value goes into the first month of the current quarter where I need to see each month represented with its own calculation.

What the calculation is doing, is taking all of our closed opportunities in the dataset and finding the win rate %. Then it is using that percentage found and multiplying the total ARR for our pipeline opps for the current quarter. We are allying the percentage as a weighting to show the weighted value of our pipeline deals for the quarter based on our historical win rate %.

Again any help would be greatly appreciated if you can advise what I should update and or change about the below Beast Mode formula to allow the bar chart to show the total weighting by month for opps closing in that month?

(SUM(case when `Stage` = 'Closed Won' AND `Primary Quote` is not null then `MDR New ARR` else 0 end) / SUM(case when `Closed` = 'true' AND `Primary Quote` is not null then `MDR New ARR` else 0 end)) * (SUM((case when QUARTER(CURRENT_DATE()) AND YEAR(CURRENT_DATE()) AND `Closed` = 'false' AND `Primary Quote` is not null then `MDR New ARR` else 0 end)))

Shawn

GrantSmith

Hi @reichner015

How is your underlying data formatted? Is your data broke out by month or by quarter or day?

reichner015

@GrantSmith

the data is coming from salesforce into a opportunity dataset. The main date field in question is close date which would be a day.

thank you

shawn

DataMaven

You need to play with your parentheses a bit, I think. Depending on exactly what maths you are looking to achieve, this may help. Though, this assumes that you are including the multiplication step in the denominator. Not sure your order of operations.

Throwing the sum around all of it may help with the date issues because it will need to aggregate with all those case statements in play. I spaced it out to hopefully help.

SUM(

(SUM(case when `Stage` = 'Closed Won' AND `Primary Quote` is not null then

 `MDR New ARR` else 0 end))

/

(

(SUM(case when `Closed` = 'true' AND `Primary Quote` is not null then `MDR New ARR` else 0 end))

 *

(SUM(case when QUARTER(CURRENT_DATE()) AND YEAR(CURRENT_DATE()) AND `Closed` = 'false' AND `Primary Quote` is not null then `MDR New ARR` else 0 end))

))

reichner015

@DataMaven Thank you for your comment, that was helpful, however I am still not seeing how to break it down for each month in the current quarter. Looks like it is still grouping by quarter in your example.

I need to somehow within that current quarter find month one of the current quarter in a dynamic way, then month 2 and lastly month 3 of the current quarter.

Any additional thoughts my friend?

Shawn

GrantSmith

Hi @reichner015

Are you selecting to view your data for the current quarter by month in the date selector (upper right of your chart)? Are you using your opportunity date as your x-axis on you chart?

Also with your beast mode `QUARTER(CURRENT_DATE()) AND YEAR(CURRENT_DATE())` isn't really doing anything as it will always return a non-0 number which is treated as True. Are you wanting to compare your date field to the current date? You'd need to do something like this:

QUARTER(CURRENT_DATE())=QUARTER(`date`) AND YEAR(CURRENT_DATE())=(`date`)

jaeW_at_Onyx

@reichner015 .

Grant has good advice.

DataMaven's beast mode is problematic b/c she's using nested SUM() functions without an OVER clause which will have unexpected (if any) results.

1) be clear about what you want to show. do you want to see one row per quarter or one row per month? (start with a table card, and build each piece of your math as separate beast modes so you can manually do the multiplication / division and ascertain whether you're getting the desired results for each component).

2) keep in mind that unless you use a window function, your beast mode can only access the data in the row that it's in. i.e. if you are showing months, you can only access opportunities that closed in that month (and therefore could not calculate a cumulative or quarterly total without the use of a window function).

3) keep in mind the rules of basic math. if you multiply or divide by zero ... your result will be zero or undefined so make sure your components don't resolve to zero.

reichner015

@GrantSmith Grant, thank you sir, and to answer your question the date picker in the top right of the card is set to all time. I am sorry if I was not clear, but in my initial ask, that was broken down a little. The reason for this is if you look at the start of my beast mode, the win rate calculation is used for "ALL Time" to get our percentage multiplier to apply to the opps for the current quarter to get a weighted forecast for the quarter.

I also need to then show that same current quarter, but need to see that calculation using the "All Time" win rate for each month of the current quarter. Hope that helps make it clearer so I need to show a currency field showing the weighted value for all opps closing in the current quarter, and then three more single number cards showing the weighted value for opps closing in month 1, 2 and 3 in the current quarter.

Help? Thanks for replying and trying to help sir,

Shawn

reichner015

Really trying to get an automated way to isolate Month 1, Month 2 and Month 3 of the current quarter. Otherwise it seems I will need to do manual entries of start and end dates of each month, and I want this to be future proof if you will, and not have to re-visit and make changes/updates each quarter.

Thanks,

Shawn

jaeW_at_Onyx

@reichner015

you can use pieces of everyone's advice to solve this.

sum(sum(amount)) over () 

will create a window function that allows you to look over all the rows in your dataset. build your CASE statement in place of amount to calculate your lifetime win rate.

to isolate transactions in the n-th month of the quarter, i would build a date dimension (one row for every day since the dawn of time) that in includes the quarter number and month of quarter and a binary column (1 or 0) called isCurrentQuarter to indicate if it's the current quarter.

Alternatively you can build an insanely complex beast mode. if the transaction took place in the 8th month, 3 months per quarter then MOD(8/3) +1 = 3

if you were in the 9th month then MOD(9 / 3) + 1 = 1.

MOD being the function modulo which gives you the remainder.

reichner015

@jaeW_at_Onyx Is there any chance you may be able to show this in an example sir? I am very new to domo and the beast mode with no real training, only learning by being shown at this point. Any help would be greatly appreciated!

Thanks,

Shawn

jaeW_at_Onyx

https://www.youtube.com/watch?v=eifSYZIcPzg

@reichner015, here's a youtube video i posted where i dive at length into Window Functions (it is a feature you'll have to ask your CSM to enable).

I am available for mentoring and consulting services through my company OnyxReporting.

DataMaven

And yes - In my beast mode comment, I was just breaking it down differently, using what was already typed. I didn't make an example and test it or anything.