How many orders that contain just 1 line item? (1 Item Order Count)

swagner

Looking for some help on a beast mode.  Here is a short video I put together so you can see what I'm trying to accomplish.

 

In my dataset I have line item detail on orders we enter daily.  We currently have beast modes for the number of orders, the number of lines, and the average lines per order.  I need to create a new beast mode to count the number of orders that contain just 1 line item.  Below are the current beast modes I have (in case that helps you understand my dataset).

 

Current Beast Modes

• Number of Orders:  COUNT(DISTINCT `Order Number`)
• Number of Lines:  COUNT( `2nd Item Number`)
• Avg Lines per Order:

  CASE
  WHEN IFNULL(COUNT(DISTINCT `Order Number`),0)=0
  THEN 0
  ELSE
  COUNT(`2nd Item Number`)
   /
      COUNT(DISTINCT `Order Number`)
  END

Chips

Morning @swagner, great video, very helpful in understanding the issue. 

 

That being said, I am sure you have already thought of the following solution, but it is the first thing that came to mind for me. 

 

Could you create a beastmode something like...

 

IFNULL(COUNT(DISTINCT CASE WHEN 'Lines' = 1 THEN 'Order Number' END ),0)

swagner

There isn't a field in my dataset named `lines`.  I have a beast mode now "number of lines" (details in my orginal post).  Is there a way to alter the solution you provided to use my "number of lines" beast mode?

Chips

How is the 'Lines' column on your drill through page populated? 

swagner

With the beast mode:

• Number of Lines:  COUNT( `2nd Item Number`)

ST_Superman

I'm not sure this is something that can be accomplished within a beastmode.  You could potentially create a summary number for the drill through page that would tell you how many for that day:

 

COUNT(DISTINCT CASE WHEN COUNT(`2nd Item Number`)=1 THEN `Order Number` END)

 

However, I don't think this beastmode would work when it is rolled up to the first view.  

 

I think that you would need to create this field in the data set, attached to each order number.  Something like `Lines in Order`.  You could then create a beastmode that would count each order number that had 1 for lines in order.

Chips

I agree with @ST_-Superman-_, if the "Lines" column from your drill through is actually itself a beastmode, then you will need to create a "Number of Lines" via Magic ETL (or SQL if that is your fancy). I misunderstood, thinking from your video that 'Lines' was a column in your dataset. 

 

I would do a Magic ELT something like:

 

1. Split your input data set into two branches

2. Send Branch "1" to a Group By Function

2. 1. Select What Columns Identify The Grouping - "Order Number"

2. 2. Name New Column - "Number of Lines"

2. 3. Column to Aggregate - "Order Line"

2. 4. How to Aggregate - "Count"

3. Select Columns on Branch "1" - Only carry through Order Number and "Number of Lines" (that you just created)

4. Join Branch "1" back to the Original Data Set ("Branch 2") based on Order Number, effectively adding "Number of Lines" to the Original Data Set

5. Then use the beastmode concept outlined above at the card level, counting the number of orders with a "1" in "Number of Lines"