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
-
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"1
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!**0 -
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
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.
0 -
Also, the order id has as many lines as it has different items.
0 -
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"1 -
Thank you! That makes much more sense. I appreciate your help.
0 -
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"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive