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

RavenUSMC
RavenUSMC Member
in Magic ETL

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

Best Answer

  • Jarvis
    Jarvis Contributor
    Answer ✓

    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!

Answers

  • Jarvis
    Jarvis Contributor
    Answer ✓

    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!

  • RavenUSMC
    RavenUSMC Member

    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! 

Categories