Creating a calculated column in beast mode results in data being split up

DomoNewbie
DomoNewbie Member
in Magic ETL

Hi all - I am new to Domo and experiencing some issues displaying desired results in a bullet chart.

I have a beast mode calculation where it provides a value if one criteria is met, and another when a different criteria is met. In my bullet bar graph, when I select both criterias in the filters, values are now split into two sets where I'm expecting one consolidated total.

I also tried capturing both criterias in the case statement but it still displayed both sets. Am I writing the beast calculation incorrectly or should I be creating this formula in the ETL?


Beast mode Calc.jpg


Bullet chart - results

Bullet Chart - results.jpg


Bullet chart - desired results

Bullet Chart - expectation.jpg


Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Try removing the SUM functions in your beast mode and then in your target value change the aggregation type to SUM. I played around with the different variations and I was able to duplicate your issue when I added the SUM functions within the beast mode. Taking them out and using the SUM function in the aggregation type took away the duplication on the x-axis.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    Is there anything in your Sorting properties? I'm wondering if LOB is in your Sorting properties and that is causing the x-axis to be repeated.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • DomoNewbie
    DomoNewbie Member

    There are currently no sorts being applied.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Try removing the SUM functions in your beast mode and then in your target value change the aggregation type to SUM. I played around with the different variations and I was able to duplicate your issue when I added the SUM functions within the beast mode. Taking them out and using the SUM function in the aggregation type took away the duplication on the x-axis.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • DomoNewbie
    DomoNewbie Member

    @MarkSnodgrass the suggestion you have provided worked. I moved the aggregation from beast mode to the ETL/dataflow (via Add Formula in the Group By) and the values are not split anymore! Much thanks for your help!

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @DomoNewbie /@MarkSnodgrass

    you generally shouldn't put an aggregate function inside a CASE statement.

    the reason why your final solution works is b/c by using the aggregate functions in Analyzer, functionally you're moving the aggregate out of the CASE statement.


    you could write your beast mode as:

    sum(
CASE
WHEN `LOB` = 'WLS' and `L2` = 'INBOUND' THEN ...
...
WHEN `LOB` = 'WLN' and `L2` = 'INBOUND' THEN ...
END
)


    I always recommend writing the aggregation into the beast mode if appropriate because it forces people to use the metric 'the right way'.


    ex. sometimes it's inappropriate to use AVG() or SUM() or MAX() so might as well build a beast mode for that.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories