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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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.

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Thank you! That makes much more sense. I appreciate your help.

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited October 2021

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"