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 
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 
Braxton,
Yea, that makes sense. I wondered if that might be the problem. Thanks for your help in resolving this.
0 
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
Categories
 All Categories
 1.1K Product Ideas
 1.1K Ideas Exchange
 1.2K Connect
 966 Connectors
 254 Workbench
 Cloud Amplifier
 1 Federated
 2.4K Transform
 75 SQL DataFlows
 499 Datasets
 1.8K Magic ETL
 2.7K Visualize
 2.2K Charting
 360 Beast Mode
 19 Variables
 483 Automate
 101 Apps
 378 APIs & Domo Developer
 6 Workflows
 22 Predict
 6 Jupyter Workspaces
 16 R & Python Tiles
 316 Distribute
 64 Domo Everywhere
 252 Scheduled Reports
 59 Manage
 59 Governance & Security
 1 Product Release Questions
 5K Community Forums
 37 Getting Started
 23 Community Member Introductions
 63 Community Announcements
 4.8K Archive