Aggregate Count of Line Items by Distinct Orders

user008875

Hi! I am trying to aggregate line item quantities by distinct order id. I built a Beast Mode that was validated, but I continue to get the message, "An issue has occurred during processing. We are unable to complete the request at this time." I have changed the beast mode multiple times and continue to get the same response. My current beast mode is the most simple of numerous attempts:

(CASE

WHEN

COUNT(DISTINCT `order_id`)

THEN

COUNT(SUM(`line_items_quantity`))

ELSE 'NULL'

END)

I feel confident that I have my data stuck in a loop, but am not sure how to get a count of the sum of line items per order number. Any suggestions would be greatly appreciated!

Thank you!

jaeW_at_Onyx

@user008875

if you're trying to group orders by number of items

ex. (orders with 5 or more items, orders with 2 items , orders with 1 etc), then you definitely can't take the approach you're taking. you need to create a column that counts the number of orders per item. you'll have to materialize it into your dataset (do it in ETL).

then you can do a beast mode to create your num_item_buckets using a CASE statement.

GrantSmith

You're attempting to aggregate twice (COUNT(SUM()) which Domo doesn't like.

Are you grouping based on the order ID at all? Do you have multiple records for the same order id in your dataset? In simple business terms what are you attempting to calculate? Why are you attempting to count the sum of line items? Do you just need the overall total line items?

MarkSnodgrass

I'm not sure how that beast mode is showing as validated because your WHEN statement isn't valid. Do you want to process what is in your THEN statement when COUNT(DISTINCT 'orderID') is greater than 1? If so, then you would write it as such:

WHEN COUNT(DISTINCT 'orderID') > 1 THEN ....

Also, a count of the sum of line item quantity doesn't make sense. I would suggest starting with a table try and get the numbers you are looking for. Try just having the orderID as a column and then drag in orderID again, but then choose the Aggregation type of count. See what those totals look like for you. Then add your quantity field and try the count or sum aggregation and see if either of those numbers work for you.

Once you have created the fields, then change the card type to the visualization you want to use for your final display.

user008875

I need to bucket the quantity of items per order, so I think I need a count of the sum. I have summed the number of items per order, but as it is a calculated field, it will not allow me to group (bucket) that, or at least I have not been able to figure that part out yet. Therefore, I end up with over 16,000 bars versus the 10 I would like.

Needless to say, I am new to this.

user008875

Also, the order id has as many lines as it has different items.

jaeW_at_Onyx

@user008875

if you're trying to group orders by number of items

ex. (orders with 5 or more items, orders with 2 items , orders with 1 etc), then you definitely can't take the approach you're taking. you need to create a column that counts the number of orders per item. you'll have to materialize it into your dataset (do it in ETL).

then you can do a beast mode to create your num_item_buckets using a CASE statement.

user008875

Thank you! That makes much more sense. I appreciate your help.

jaeW_at_Onyx

NP. If you're looking for more Domo training or upskilling, I post tutorials and content here: https://datacrew.circle.so/home