Beast Mode validated but not working
My Beast Mode shows it is validated, but then the numbers will not display on my graph. The error message I get says "An issues has occured during processing. We are unable to complete the request at this time."
How do I fix this beast mode to get it to work properly?
SUM(`Item No`) / Count (Distinct `Customer ID`)
or I tried
`Item No` / Count (Distinct `Customer ID`)
and neither works.
My end result needs to show the number of items bought per unique customers.
Thank you,
Nicole
Best Answer
-
Is your `Item No` a text field? If so, you probably can't sum that.
Try COUNT(`Item No`) / COUNT(DISTINCT `Customer ID`)
That would be to show the number of products purchased by customer.
Depending on the use case, you might also look at SUM([quantity column]) / COUNT(DISTINCT `Customer ID`)
That would get you the quantity of units instead of number of products.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
Is your `Item No` a text field? If so, you probably can't sum that.
Try COUNT(`Item No`) / COUNT(DISTINCT `Customer ID`)
That would be to show the number of products purchased by customer.
Depending on the use case, you might also look at SUM([quantity column]) / COUNT(DISTINCT `Customer ID`)
That would get you the quantity of units instead of number of products.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
Yes! Thank you! It worked.
1 -
I'm getting the same issue with this Beast Mode calculation (formula validates but when entered into a chart, reads "an issue has occurred during processing. We are unable ...ect.". However, any of the solutions proposed earlier did not apply to mine. Please help.
(CASE WHEN `acquisition_cost` > 1 THEN AVG(`acquisition_cost`) END) * (COUNT(`property_id`))
0 -
If you want to do an aggregation on data where certain conditions apply, you usually have to do the aggregation on the outside of the case statement instead of on the inside.
Yours should look more like this:
AVG(CASE WHEN `acquisition_cost` > 1 THEN `acquisition_cost` END)
*
COUNT(`property_id')
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Took some additional finagling, but I was eventually able to get it. Thanks for the assist
0 -
Hi, I am also having the same problem but none of the above work
(CASE WHEN (`adname` LIKE '%asset:%') THEN SUBSTRING(`adname`,(INSTR(`adname`,'asset:') + 6),(INSTR(`adname`,'_type') - (INSTR(`adname`,'asset:') + 6))) END )
0 -
Does your beast mode validate but not return what you expect? Or does it return nothing at all? Conceptually it looks ok. Might help if you provided a sample `adname` value.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
I want to count the distinct service invoices I have when the category is service. I wrote my case statement in the following way
(CASE
when `Category` = 'service' then COUNT(DISTINCT `Invoiceno`) end
)I don't know what's wrong. Please help. This also happened in the following cases.
CASE WHEN ‘promotions’ IS NOT NULL THEN SUM(‘net amt’) END)
(CASE
when `Gender` ='Female' then COUNT(DISTINCT `Customer Code`) else 0 end
)
(CASE
when `Gender` ='Female' then COUNT(DISTINCT `Customer Code`) else 0 end
)
Thank you.
0 -
As Aaron mentioned earlier in this post (thank you @AS), you need to place the Aggregate function on the outside of your case statement.
COUNT(DISTINCT (CASE
when `Category` = 'service' then `Invoiceno` end))Same with the others:
SUM(CASE WHEN ‘promotions’ IS NOT NULL THEN ‘net amt’ END)
COUNT(DISTINCT (CASE when `Gender` ='Female' then `Customer Code` else 0 end))
COUNT(DISTINCT (CASE when `Gender` ='Female' then `Customer Code` else 0 end))
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman2 -
Right. In all of these cases we have to remember that beast mode is looking at one row of data at a time, and iterating through the rows in a loop as it aggregates. That's not the same thing as counting all rows at once but only if certain conditions exist. That's how the brain thinks of the situation typically, but not beast mode.
Because beast mode processes one row at a time, and these different calculation versions all have an aggregation on the inside of the case statement, beast mode is trying to aggregate on just one row, and then probably gets stuck essentially aggregating within a loop that doesn't finish like you think it would.
So, think of conditional counting in beast mode more like this:
"aggregate this if a condition is true" (SUM... CASE)
NOT like this:
"if a condition is true, aggregate this" (CASE... SUM)
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"3 -
In my case, it was a misplaced parenthesis:
Wrong: SUBSTRING(SEC_TO_TIME(`Combined AOE`, 1, 2))
Right: SUBSTRING(SEC_TO_TIME(`Combined AOE`), 1, 2)
However, I might suggest Domo re-word the message. When I read a message like, "Unable to ... at this time", I tend to assume it's a back-end issue. Maybe something more along the lines of, "Unable to properly parse your formula"?
Thanks to this thread for letting me know it was my fault. ?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive