Beast Mode validated but not working

user009400
user009400 Member
in Magic ETL

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

  • AS
    AS Coach
    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"

Answers

  • AS
    AS Coach
    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"
  • user009400
    user009400 Member

    Yes! Thank you! It worked.

  • user07605
    user07605 Member

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

    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"
  • user07605
    user07605 Member

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

  • user009745
    user009745 Member

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

    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"
  • user08600
    user08600 Member

    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-_
    ST_-Superman-_ Coach

    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.” -Superman
  • AS
    AS Coach

    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"
  • user056645
    user056645 Member

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

Categories