Creating a Beast Mode to calculate field sums for remainder of year

markb
markb Member
in Beast Mode

Hi - I am trying to write a beast mode calculation that sums a field from an as of date until the end of the year. The variable is indexed across the year and want to count how many are left depending on what date is entered. For instance if 1/22/24 was the date it would count all the values indexed for the variable until 12/31/24 and so on.

Here is what I have but doesnt seem to fully work.

SUM(CASE WHEN index >= date AND index <= LAST_DAY(CURRENT_DATE() + INTERVAL (12 - MONTH(CURRENT_DATE())) MONTH) THEN (variable) END)

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    an easy way to get the last day of the year is to do this:

    DATE(CONCAT('12/31/',YEAR(CURRENT_DATE())))

    You can put this in place of your 2nd date piece.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    an easy way to get the last day of the year is to do this:

    DATE(CONCAT('12/31/',YEAR(CURRENT_DATE())))

    You can put this in place of your 2nd date piece.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    What issue are you experiencing? How is it not working? Have you tried splitting out your conditions into separate beast modes and using a table card to see the values for each row of data?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • markb
    markb Member

    @MarkSnodgrass thanks for the suggestion. Unfortunately when i make that update i get an error saying "An issue has occurred during processing. We are unable to complete the request at this time."

    @GrantSmith the current equation isn't aggregating and calculating the totals correctly with the snippet I first mentioned. Can you elaborate further on the last question?

  • MarkSnodgrass
    MarkSnodgrass Coach

    Try this, though you'll need to re-type the index and variable fieldnames if they don't paste in correctly.

    SUM(CASE WHEN index >= date AND index <= DATE(CONCAT('12/31/',YEAR(CURRENT_DATE()))) THEN variable END)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • markb
    markb Member

    I retyped the field names when I copied this over but I needed to change current_date to the date field I have since some of the data is in past years so it pulls correctly. but still got the same error where the chart wont load

  • markb
    markb Member

    I realized I didn't replace the other current_date references with date. it worked this time

    thanks for the assistance!

Categories