How to create a beast mode calculation that can do a count and then sum

I’m currently working on an issue dealing with conducting a filter than an aggregate in beast mode. (I will say at this point that I have a feeling that this may not be done in Beast Mode.)

I have my data that looks something like this:

Col A Col B Col C (What I need to create)

B 2 0.2

G 4 0.4

H 4 0.4

The total of Col B is 10 and this will be needed to calculate Col C. Also column B is the count of column A-meaning how many times the letter B or letter G appears.

Currently, I have no way to get the running total of col B which I need to calculate the percentage from Col A like the following: 2 B’s and that divided by the total, 10, will give me 0.2.

I’ve been trying to write a Beast mode calculation on this and have something similar to this:

(CASE

WHEN `Col A` = 'B' THEN COUNT(`B`) / SUM(COUNT(`B`))

WHEN `Col A` = 'G' THEN COUNT(`B`) / SUM(COUNT(`B`))

WHEN `Col A` = 'H' THEN COUNT(`B`) / SUM(COUNT(`B`))

END)

I know that the SUM on a COUNT gives me the error message and will not validate.

However, as I know from looking at the first below link, I cannot do a filter than an aggregate in Beast Mode. However, the reply came in over two years ago and hoping that there’s been a solution since then. Does anyone have any idea how to modify my code so that I can do this in beast mode? Or does the data have to be modified elsewhere? (Such as in SQL?) Thank you for any help that may be provided.

Other Links that I’ve looked at for a solution:

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/How-can-I-use-a-beast-mode-calculation-as-a-filter/td-p/14798

https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Is-a-Case-Statement-using-COUNT-SUM-possible/td-p/30627

Find more posts tagged with

Accepted answers

Jarvis

Hey user09207,

In this circumstance, I would personally use a dataflow to manipulate these values. However, you may want to reach out to your Domo account team if you wanted to confirm what can be accomplished via beast mode here.

Thanks!

All comments

Jarvis

Hey user09207,

Thanks!

RavenUSMC

Good day Jarvis,

Thank you for your response! At my office we discussed doing exactly that. We also found that the values that we were trying to get could be gotten by using a graph but not the table object. I originally had all the data in the DOMO table chart type and that was not working for me. However, when I moved to a bar graph I could get a percentage of the whole. Without a doubt, in the future, I believe using a dataflow to manipulate the values would be the best course. I just wish DOMO's beast mode would allow you to do this! Thank you again for your response!