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
 10.5K All Categories
 8 Connect
 918 Connectors
 250 Workbench
 469 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 477 Datasets
 193 Visualize
 252 Beast Mode
 2.1K Charting
 11 Variables
 17 Automate
 354 APIs & Domo Developer
 89 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 247 Distribute
 63 Domo Everywhere
 243 Scheduled Reports
 21 Manage
 42 Governance & Security
 174 Product Ideas
 1.2K Ideas Exchange
 12 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive