Aggregate Count of Line Items by Distinct Orders

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!

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    @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.

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    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
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • 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.

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

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    edited October 13

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