Beastmode Help: Determine if order is <= $50,000

swagner
swagner Contributor
in Magic ETL

I've got a dataset that contains order data at the line item level.  An order could have 1, or many items.  I need to create a flag column that indicates if the total order value is <= 50,000. Would like to do this with a Beastmode if possible.

 

The unique identifier in this case is the order number, and the extended price is the column that contains the value for each item on the order that needs to be totalled to calculate the total order value.

 

Example attached.

 

Thanks in advance for your help!

 

 

Best Answers

  • GTA
    GTA Domo Employee
    Answer ✓

    I absolutely get where you are going with the ask. In order to filter and search on that flag, I'd recommend that you run your dataset through a Magic ETL DataFlow and use a window function to partition based on the unique order number, like you mentioned. You can find documentation for that here- https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

     

    This will add on another column on your dataset that will tell you that total. Then you can build a simple case statement to flag based on that $50,000 rule. 

  • swagner
    swagner Contributor
    Answer ✓

    @GTA thanks again for the feedback.  I have inplemented the solution you offered.

     

    Here is a short video overview of how I implemented the Window function in case others want to see this in action.

Answers

  • GTA
    GTA Domo Employee
    Answer ✓

    I absolutely get where you are going with the ask. In order to filter and search on that flag, I'd recommend that you run your dataset through a Magic ETL DataFlow and use a window function to partition based on the unique order number, like you mentioned. You can find documentation for that here- https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window

     

    This will add on another column on your dataset that will tell you that total. Then you can build a simple case statement to flag based on that $50,000 rule. 

  • swagner
    swagner Contributor

    Thanks for the quick reply!

     

    Really hoping to find a solution for this with a Beastmode.  Would be a much faster solution to implement if it's possible.

  • swagner
    swagner Contributor
    Answer ✓

    @GTA thanks again for the feedback.  I have inplemented the solution you offered.

     

    Here is a short video overview of how I implemented the Window function in case others want to see this in action.

  • swagner
    swagner Contributor

    @DaniBoy wanted to be sure you saw how this turned out.  Thanks as always for your help.  Great seeing you in ATL yesterday sir!  Safe travels home, and please have a great weekend!

  • [Deleted User]
    [Deleted User] Member

    @swagner

     

    Great seeing you in ATL and thank you for making the drive down!

     

    This is great!!

     

    CC: @tallbaldfilmguy @hrh514 from our Domo Edcuation team

  • rado98
    rado98 Contributor

    Hi

     

    You could use a Beastmode but it would not work unless you group by Order on the card (like the second box on you xls example.

     

    Case when sum($) > 50000 then 'Yes' else 'No' end

Categories