Beast Mode calculation works but when trying to insert into a card, I get an error.

AZaiter

Good day,

I have a beast mode calculation that I believe can calculate the sum of accidents over the previous 12 months for any given month. My data set goes back 3 years on a month by month basis. Here is the current beast mode that works when I validate it:

SUM(SUM(fieldsAccident Count)) OVER (
PARTITION BY YEAR(fieldsMonth Ending Date)
ORDER BY fieldsMonth Ending Date
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
)

However, when trying to insert the beast mode into any visual/card, I get the following message in the Analyzer:

An issue has occurred during processing. We are unable to complete the request at this time. 

What is potentially wrong with my beast mode and what can I do to fix it?

ColemenWilson

I think it is because of the SUM(SUM part. If you remove the extra SUM does it work?

You could try:
SUM(CASE WHEN `fieldsMonth Ending Date` > CURRENT_DATE() - INTERVAL 12 MONTH THEN `fieldsAccident Count` ELSE 0 END)

AZaiter

https://community-forums.domo.com/main/discussion/comment/69108#Comment_69108

Your formula works but I don't think it accomplished what I am trying to do.

If right now it is June 2023, I want my most recent data point calculated to be the sum of all accidents from June 2022-June 2023. Right under that for May 2023, it will display the sum of all accidents from May 2022-May 2023, etc. The idea is to show each month as the sum of the previous 12 months, if that makes sense.

The formula above checks if the associated month is within the last 12 months, then displays the total for just that month, whatever it may be. I am looking for the total to be the sum of the last 12 months for each given month. Kind of tricky to explain but I hope that helps!

MichelleH

@AZaiter It looks like you're running into the issue because you are partitioning by year. So effectively it is taking the sum over the last 12 years, not months. Try partitioning and ordering by the last day of the month like this:

SUM(SUM(fieldsAccident Count)) OVER (
PARTITION BY LAST_DAY(fieldsMonth Ending Date)
ORDER BY LAST_DAY(fieldsMonth Ending Date)
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
)

AZaiter

https://community-forums.domo.com/main/discussion/comment/69229#Comment_69229

Thank you for this. The beast mode was approved but unfortunately I am getting this same error when trying to insert into a table or graph. Still not sure why.

MichelleH

@AZaiter How are you trying to present this metric in your card?

AZaiter

https://community-forums.domo.com/main/discussion/comment/69238#Comment_69238

Ultimately on a line graph. So basically a line graph that has the last 12 months (June 2022 - June 2023) on the X axis and each point for each month on the line graph is the sum of each month's accidents over the previous 12 months.

So for example, if the sum of all accidents from June 2022 - June 2023 is 100 accidents, the point on the line graph for June 2023 will be 100. if the sum of all accidents from May 2022 - May 2023 is 90, the May 2023 point of the line graph will be at 90, etc. I want the beginning of the graph to be June 2022, which will then show the total sum of accidents from June 2021 - June 2022.

All of this should be simple within the graph to display the data over 1 year, and the field I have called 'fieldsMonth Ending Date' is always the last date of a specific month for the given year. So the most recent point just says 'June 30, 2023' and right below that is 'May 31, 2023).

The days itself are not relevant, but the month column is basically telling us which month and year the accident happened. That is the basis of all sums I need.

MichelleH

@AZaiter That helps to know that you want to graph by month. I suspect the issue has to do with the date granularities of your calculation and the groupings in your card. Try removing the partition portion of your beast mode, since graphing by month makes partitioning by month obsolete.

AZaiter

https://community-forums.domo.com/main/discussion/comment/69248#Comment_69248

Thanks for the help. I think we are getting closer but something is still off. Removing the partition clause in the beast mode, It currently is looking like this:

SUM(fieldsAccident Count) OVER (
ORDER BY fieldsMonth Ending Date
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
)

Unfortunately, I am still getting that general error when I try and insert it in any visual. If it helps my original question, here is a sample of the data I am trying to work with regarding accidents:

What you see is the sum of all accidents per month. May 2023 we had 12 accidents, April 2023 had 14, etc. If we sum above May 2022 - May 2023, the total is 122. The data point on my line graph for May 2023 should be 122, April 2023 should then be 119, etc on this rolling basis.

For testing this beast mode my expectation is to have the calculation as a 3rd column on this table, with similar totals to how I just explained. Each row is the sum of the previous 12 months accidents relative to the starting month. Logically the formulas we are working with make sense to me, but for some reason, though approving in Domo, error out in the visuals when I try to insert them.

MichelleH

@AZaiter Just to confirm, do you have beast mode window function enabled in your instance and have they worked for you before? Also, you will need to add the outer SUM() back to your formula since that is required for window functions in beast mode.

AZaiter

https://community-forums.domo.com/main/discussion/comment/69269#Comment_69269

Adding in the outer sum function was approved but still getting the error when inserting into a visual:

SUM(SUM(fieldsAccident Count)) OVER (
ORDER BY LAST_DAY(fieldsMonth Ending Date)
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
)

Can you please elaborate more on beast mode window function enabling? Not sure what that is, but it might be the root cause here.

MichelleH

@AZaiter Not all instances allow window functions in beast modes. If you have not used them successfully before, then I'd suggest reaching out to your Domo account team to confirm and get them enabled.

AZaiter

https://community-forums.domo.com/main/discussion/comment/69271#Comment_69271

We might ultimately have to do that, but still not sure why the Beast Mode functions you guys provided are not running, unless the only logical reason is related to window functions.

It's a pretty basic calculation I am trying to make and Domo should allow it to divide the 12 month rolling sum by month in a line graph. For some reason though, the verified calculation errors out when trying to put it in any visual, even a single value card. After doing more research across the internet yesterday afternoon, I am 95% sure the last function presented is consistently what we need:

SUM(SUM(fieldsAccident Count)) OVER (
ORDER BY LAST_DAY(fieldsMonth Ending Date)
ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
)

But Domo isn't budging on it, at least my organizations version. We might have to contact our Domo rep over the phone and explain directly to them to find a direct solution.