Need some help: Two Beast Modes

swagner

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!

zcameron

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-_

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.

 

 

 

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

 

 

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

 

 

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

 

 

 

 

 

 

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

 

 

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 section

 

 

 

zcameron

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

Thanks!  Working through this now.

ST_-Superman-_

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.

 

 

 

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

 

 

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

 

 

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

 

 

 

 

 

 

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

 

 

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 section

 

 

 

zcameron

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-_

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

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]

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!