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

markb · 2024-01-22T19:33:45+00:00

There was an error rendering this rich post.

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)

Find more posts tagged with

Date

Table

Count

Beast Mode

Accepted answers

MarkSnodgrass

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.

All comments

MarkSnodgrass

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.

GrantSmith

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?

markb

@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

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)

markb

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

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

thanks for the assistance!