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

jordan_platinum
jordan_platinum Member
in Magic ETL

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

Tagged:

Best Answers

  • B_Fullenkamp
    B_Fullenkamp Domo Employee
    edited August 2021 Answer ✓

    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
    jaeW_at_Onyx Coach
    Answer ✓

    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"

Answers

  • B_Fullenkamp
    B_Fullenkamp Domo Employee

    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
    GrantSmith Coach

    @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!**
  • jordan_platinum
    jordan_platinum Member

    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
    B_Fullenkamp Domo Employee
    edited August 2021 Answer ✓

    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
    jordan_platinum Member

    Braxton,

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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"

Categories