Beast Mode division calculation not work

Nek
Nek Member
in Magic ETL

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

  • ST_Superman
    ST_Superman Domo Employee

    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.

  • Nek
    Nek Member

    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!

  • Nek
    Nek Member

    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

Categories