Is there an easy way to calculate the days left in the month to insert in a Beast Mode equation?
I'm working on a Beast Mode where I have a given an amount of production that I want to divide by the remaining days of the month. The method I have below doesn't function the way I want it to. My code segment is below. Is there any easier way to go about doing this? Thanks in advance.
(SUM(`Goal`)  SUM(`Production`)) / (LAST_DAY(CURDATE())DAYOFMONTH(CURDATE()))
Best Answers

Hey @jordan_platinum
That is because curdate() (at least in my timezone) is 20210831 so therefore it is the last day of the month and the portion of the beastmode after the division symbol is evaluating to a value of 1 so Domo is taking the value of 1.17M and dividing it by 1. Tomorrow the value would evaluate to 30 since there are 30 days in September.
Note, this formula is only going to work for the current month because of the curdate() formula. If you were looking to do this for future months you would need to replace curdate() with a date field in your data.
Does that make sense?
1 
i'm late to the party ... but just wanted to put this out there... all 3 of you are mixing math before and after aggregation and while it probably works ... it isn't best practices and you should be careful of unexpected results
@GrantSmith 's solution is pretty solid except you're conducting you CASE statement after aggregation. even INSIDE the ELSE clause you're calculating DAYOFMONTH etc. after aggregation. again... while that works b/c you happen to be using curdate() which will always evaluate to the same value, a lot of times people make the mistake of trying to use a field ex. DAYOFMONTH(LAST_DAY(trans_date)) ... AFTER aggregation, but if date isn't on the dataset, you'll get the 'wrong result'.
CASE WHEN LAST_DAY(CURDATE()) = DAYOFMONTH(CURDATE()) THEN 0 ELSE (SUM(`Goal`)  SUM(`Production`)) / (DAYOFMONTH(LAST_DAY(CURDATE()))  DAYOFMONTH(CURDATE()) END
the better solution is to either perform case statement where all the arguments are wrapped in an aggregate function, OR better yet, perform your CASE statement inside the aggregation.
sum( case when dayofmonth(...) then ... else ... end )  sum (case when ... ) / sum(case when ... )
in other words, keep in mind that analyzer converts your query into SQL. if you don't know with 100% certainty what the resulting SQL query will be based on your beast mode ... it's likely that you're gonna get unexpected results ... or at least difficult to predict accurately results.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers

Hi Jordan,
You are super close, I think you just need to change the last part of your beastmode to look like this:
DAYOFMONTH(LAST_DAY(CURDATE()))  DAYOFMONTH(CURDATE())
Note, because today is the last day of the month it currently evaluates to 0 so this would give you a divide by 0 error. You may want to add 1 to the result so that you always end up with a minimum value of 1.
So your final beastmode would look like this:
(SUM(`Goal`)  SUM(`Production`)) / (DAYOFMONTH(LAST_DAY(CURDATE()))  DAYOFMONTH(CURDATE()) + 1)
Give that a try and let me know how it works!
Braxton Fullenkamp
0 
@B_Fullenkamp beat me to it. I'd recommend his method with a slight tweak to properly handle the last day of the month:
CASE WHEN LAST_DAY(CURDATE()) = DAYOFMONTH(CURDATE()) THEN 0 ELSE (SUM(`Goal`)  SUM(`Production`)) / (DAYOFMONTH(LAST_DAY(CURDATE()))  DAYOFMONTH(CURDATE()) END
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Hey Braxton,
Thanks for your help. I'm still having the same issue with that update.
So for example, in the month of September the goal is 2.82 M and the production we have already schedules is 1.65 M, thus, we are short 1.17 M of the goal. So I want to divide that by 30 and get the end result of needing 55K a day scheduled to meet the goal. However, the formula that I did, and the updated one you gave me just spits back the 1.17 M.
I hope that explanation makes sense. So basically the beastmode equation we are working on gives the same number as:
(SUM(`Goal`)  SUM(`Production`))
Thus, it seems that the date calculation isn't even being applied. Do you have any idea why that might be the case? Thanks in advance.
Jordan Brasher
0 
Braxton,
Yea, that makes sense. I wondered if that might be the problem. Thanks for your help in resolving this.
0
Categories
 7.7K All Categories
 5 Connect
 921 Connectors
 244 Workbench
 477 Transform
 1.8K Magic ETL
 60 SQL DataFlows
 446 Datasets
 40 Visualize
 199 Beast Mode
 2K Charting
 8 Variables
 1 Automate
 348 APIs & Domo Developer
 82 Apps
 Workflows
 14 Predict
 3 Jupyter Workspaces
 11 R & Python Tiles
 241 Distribute
 59 Domo Everywhere
 241 Scheduled Reports
 15 Manage
 36 Governance & Security
 28 Product Ideas
 1.1K Ideas Exchange
 Community Forums
 14 Getting Started
 1 Community Member Introductions
 49 Community News
 18 Event Recordings
 579 日本支部