How to have a static number in beast mode

user04089
user04089 Member
in Archive

I have a table of categories with corresponding category path and visitors. Like the table below:

IDCategory NameCategory PathVisitors% Seg
123A AirlinesTravel and Tourism -> In-Market -> Airlines1000100/10000
124B AirlinesTravel and Tourism -> In-Market -> Airlines15001500/10000
125C AirlinesTravel and Tourism -> In-Market -> Airlines20002000/10000
126D AirlinesTravel and Tourism -> In-Market -> Airlines25002500/10000
200In-MarketTravel and Tourism10000 

 

I need to create a "% of Segment" beast mode, whose calculation is Visitor (for each airline)/ Visitor (In-market), so I need my denominator to be static - 100000 - in the beast mode. The beast mode I'm having right now is `Visitor`/(case when `ID`=200 then `Visitor` end), which doesn't work. How can I have that number other than manually typing in?

 

Thank you for any idea on how to solve it!

Tagged:

Comments

  • AS
    AS Coach

    You'd need, at the very minimum, a window function for that kind of math.  I don't think these are officially supported by Domo for Beast Modes (they are in data flows), so your mileage may vary.

     

    To get your denominator it would be something like sum(`visitors`) over (partition by `category path`)

    Help link: https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

     

    You appear to have the added complexity of extracting category values from your category path column, so that may make things more difficult if you want to get those exact elements as shown here.

     

    Hopefully this gives you some direction.  Let us know if you need more help.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user04089
    user04089 Member

    Hi Aaron,

     

    Thank you for the idea! However, the visitor for "In-Market" is not the sum of other rows, which is  why I need to use that exact cell value, so I don't think I can use the function you suggested.

     

    The reason why I didn't do these calculations in data flows is because the denominators for the different categories vary, which depend on the categories filtered in the cards. If there is no available function in beast modes, I will just manully type in. Thank you!!!

Categories