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