Beast Mode division calculation not work

Hello everyone, 

 

I've read other related posts and tried with my case but still failed, see if anyone could help. Thanks in advance!

 

I try to get the % of certain rating (with 'green' or 'acceptable'), but I always get the result=2 with my formula. However, it returns the correct count when I do the numerator and denominator separately, but just failed to get the divided number. note: I need DISTINCT as the `Factory ID` in my case have mutliple records in data.

my formula is like this: 

COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`)

 

fyi., I have also tried these as suggested in some posts but also failed to get the correct result: 

i tried this: 

case when COUNT(DISTINCT `Factory ID`)=0 then 0 else
(COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`)) end

 

and also either of these - both return a super large value:

case when COUNT(DISTINCT `Factory ID`)=0 then 0 else
(COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`) over ()) end

OR

(COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`) over ()) 

 

Much appreciate if anyone can help and share your thought! THANKS in advance!

 

regards,

Nek

Comments

  • Jarvis
    Jarvis Domo Employee

    Hello Nek,

     

    If you have live support included in your package I would reach out to support directly. If a member of the support team can visually see your data and how it's working they may be able to offer you the answers you are looking for.

     

    Jarvis

  • can you show what kind of card you are using the calculation on?  I tried to create a sample data set and found your calculation performing correctly.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • thanks Jarvis! can you advise how to check if i have live support in my package? I am using company's account for DOMO. thank you!

  • thanksST_-Superman-_!

     

    my data is like factory-case-level, each factory might have multiple cases while there is the same status for each factory.
    what I want to do is to compute the % of total factories with the status named like '%green%' or '%acceptable%'

    so at first I tried a formula this way:
    numerator: to get the distinct count of factory with the required status
    denominator: to get the total distinct count of factory

     

    COUNT(DISTINCT (CASE when lower(`Latest FA Rating`) like '%acceptable%' or lower(`Latest FA Rating`) like '%green%' then `Factory ID` end)) / COUNT(DISTINCT `Factory ID`)

     

    however, it always returns 2 from this formula

     

    please let me know if any more details I should provide.

     

    thanks a lot for your help in advance!!

     

    best regards,
    Nek