Beast Mode validated but not working

user009400

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

 

 

 

 

 

AS

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.

AS

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.

user009400

Yes! Thank you! It worked.

user07605

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`))

AS

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')

 

 

user07605

Took some additional finagling, but I was eventually able to get it. Thanks for the assist

user009745

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 )

AS

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.

user08600

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.

ST_Superman

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))

AS

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)

user056645

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