Need some help: Two Beast Modes

swagner
swagner Contributor
in Magic ETL

I've got two Beast Mode calculations related to supplier receiving measures I am having trouble with.  Hoping you can help me transfer my Excel knowledge in the example here over to Beast Mode. 

 

  • Attached is a workbook with sample data, example of the finished report, and explainations of the calculations.
  • Here is a video I put together to overview what I am trying to do, and more explaination.

 

The two Beast Modes I am needing help with are:

  • Initial Fill Rate = Number of Lines Received First Receiving Day / Supplier Lines Received
  • Average Shipments to Complete Order = Number of Receiving Events / Supplier Order Count

Thanks in advance for your help!

Best Answers

  • zcameron
    zcameron Domo Employee
    Answer ✓

    swagner,

     

    The type of calculation you're hoping to do would need a little pre-work in a dataflow before writing the beast mode. Luckily, it isn't too tricky to do.

     

    Here's a description of the Magic ETL dataflow:

    Group the receiving data by "Order Number" and "Supplier", creating fields for Total Lines Received (SUM of Receiving Lines) and Date of First Receipt (MIN of Receipt Date) and then Join the result back onto the original data. Then we filter the data to distinguish between initial receipts and subsequent receipts and label them accordingly. Then we bring the data back together and output it. (See attached image for steps required).

     

    Now you can create a beast mode for the Initial Fill Rate:

     

    SUM(
      CASE 
        WHEN `First Receipt` = 'TRUE' THEN `Receiving Lines`
        ELSE 0
      END
    )
    /
    SUM(`Receiving Lines`)

    The second beast mode (Average Shipments to Complete Order) doesn't require any prework. It could be simply built like this:

    COUNT(`Order Number`)
    /
    COUNT(DISTINCT `Order Number`)

     

    I hope that helps!

     

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    This request cannot be completed with a beast mode alone.  Beast modes cannot perform functions to look accross multiple rows of data.  You first need to add a field to your data set that will provide the First Receiving Day for each Order Number.  I did this with an ETL:

     

    I began by using your supplied data set as the input data set.1.png

     

     

     

    I then used the "Group By" action to create a new field, "First Receiving Day", for each Order Number:2.png

     

     

    Then you LEFT join the new field onto the original data set.3.png

     

     

    The Last step is to remove the duplicate fields with the Select Columns action and the create the output data set.

    4.png

     

     

    5.png

     

     

     

     

    Now we can re-create your metrics using beast modes.6.png

     

     

    1: Receiving Lines

    This is just the SUM of the field `Receiving Lines`

     

    2: Lines Received First Receiving Day

    This is a beast mode calculation.

    sum(case when `Receipt Date` = `First Receiving Day` then `Receiving Lines` else 0 end)

     

    3: Initial Fill Rate

    Another Beast mode.

    sum(case when `Receipt Date` = `First Receiving Day` then `Receiving Lines` else 0 end) / sum(`Receiving Lines`)

     

    4: Receiving Events

    count(`Receipt Date`)

     

    5: Supplier Order Count

    COUNT(DISTINCT `Order Number`)

     

    6: Avg Shipments to Complete Order

    (count(`Receipt Date`) / COUNT(DISTINCT `Order Number`))

     

     

    As a side note, when you build the card, you will need to add the Supplier field to the Sorting section7.png

     

     

     

Answers

  • zcameron
    zcameron Domo Employee
    Answer ✓

    swagner,

     

    The type of calculation you're hoping to do would need a little pre-work in a dataflow before writing the beast mode. Luckily, it isn't too tricky to do.

     

    Here's a description of the Magic ETL dataflow:

    Group the receiving data by "Order Number" and "Supplier", creating fields for Total Lines Received (SUM of Receiving Lines) and Date of First Receipt (MIN of Receipt Date) and then Join the result back onto the original data. Then we filter the data to distinguish between initial receipts and subsequent receipts and label them accordingly. Then we bring the data back together and output it. (See attached image for steps required).

     

    Now you can create a beast mode for the Initial Fill Rate:

     

    SUM(
      CASE 
        WHEN `First Receipt` = 'TRUE' THEN `Receiving Lines`
        ELSE 0
      END
    )
    /
    SUM(`Receiving Lines`)

    The second beast mode (Average Shipments to Complete Order) doesn't require any prework. It could be simply built like this:

    COUNT(`Order Number`)
    /
    COUNT(DISTINCT `Order Number`)

     

    I hope that helps!

     

  • swagner
    swagner Contributor

    Thanks!  Working through this now.

  • ST_Superman
    ST_Superman Domo Employee
    Answer ✓

    This request cannot be completed with a beast mode alone.  Beast modes cannot perform functions to look accross multiple rows of data.  You first need to add a field to your data set that will provide the First Receiving Day for each Order Number.  I did this with an ETL:

     

    I began by using your supplied data set as the input data set.1.png

     

     

     

    I then used the "Group By" action to create a new field, "First Receiving Day", for each Order Number:2.png

     

     

    Then you LEFT join the new field onto the original data set.3.png

     

     

    The Last step is to remove the duplicate fields with the Select Columns action and the create the output data set.

    4.png

     

     

    5.png

     

     

     

     

    Now we can re-create your metrics using beast modes.6.png

     

     

    1: Receiving Lines

    This is just the SUM of the field `Receiving Lines`

     

    2: Lines Received First Receiving Day

    This is a beast mode calculation.

    sum(case when `Receipt Date` = `First Receiving Day` then `Receiving Lines` else 0 end)

     

    3: Initial Fill Rate

    Another Beast mode.

    sum(case when `Receipt Date` = `First Receiving Day` then `Receiving Lines` else 0 end) / sum(`Receiving Lines`)

     

    4: Receiving Events

    count(`Receipt Date`)

     

    5: Supplier Order Count

    COUNT(DISTINCT `Order Number`)

     

    6: Avg Shipments to Complete Order

    (count(`Receipt Date`) / COUNT(DISTINCT `Order Number`))

     

     

    As a side note, when you build the card, you will need to add the Supplier field to the Sorting section7.png

     

     

     

  • zcameron
    zcameron Domo Employee

    For what it's worth, I like Scott's version better. Moving the checking for first receipt to the beast mode makes the dataflow simpler and as long as the data doesn't get too large, there shouldn't be a noticeable performance hit doing the calculation on the card instead of in the dataflow.

     

    Nice work, Scott!

  • ST_Superman
    ST_Superman Domo Employee

    Thanks @zcameron

    Also, for what it's worth... had I seen your post prior to working on this, I would have moved on to the next topic.  ?

     

  • swagner
    swagner Contributor

    Thank you for taking the time to provide the detailed answer!  This is awesome!  I had already started working through the solution @zcameron had provided (which worked exactally as I needed).

     

    Great to see another approach - learning opportunity!

  • [Deleted User]
    [Deleted User] Member

    Nice work fellas, great to see this collaboration!

     

    @ST_-Superman-_ great seeing you in SF at Dreamforce! 

     

    Keep up the good work, you too @zcameron!

     

    Cheers!

Categories