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

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?



Bullet chart - results


Bullet chart - desired results


Best Answer

  • MarkSnodgrass
    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

  • 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.
  • There are currently no sorts being applied.

  • MarkSnodgrass
    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.
  • @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!

  • @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"