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:

(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

Find more posts tagged with

Comments

Jarvis

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

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

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

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