Is there an easy way to calculate the days left in the month to insert in a Beast Mode equation?

jordan_platinum

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()))

B_Fullenkamp

Hey @jordan_platinum

That is because curdate() (at least in my timezone) is 2021-08-31 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?

jaeW_at_Onyx

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.

B_Fullenkamp

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

GrantSmith

@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

jordan_platinum

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

B_Fullenkamp

Hey @jordan_platinum

That is because curdate() (at least in my timezone) is 2021-08-31 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?

jordan_platinum

Braxton,

Yea, that makes sense. I wondered if that might be the problem. Thanks for your help in resolving this.

jaeW_at_Onyx

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.