Using Partition by with Count

ColinHaze · February 26

Hi,

I have this beastmode that is taking the % of people over $1000 in revenue and dividing it by the total # of people. It behaves correctly when I have only one terminal selected, however it shoots up to 350% when no terminal is selected. What is causing this? I tried removing the Partition by in the count distinct but then I receive .02%. The formula below gives me accurate numbers when only one terminal is selected.

I tried to switch the second Partition by clause to driverID and that was incorrect as well.

Any advice would be helpful!

(SUM(CASE WHEN AVG(Revenue) >= 1000 THEN 1 ELSE 0 END) OVER (PARTITION BY DriverID))
/
COUNT(DISTINCT DriverID) OVER (PARTITION BY MPP_TERMINAL_NAME)

Craig_Lynch · February 27

Have you tried using FIXED (BY MPP_TERMINAL_NAME) ?

ColinHaze · February 27

Craig_Lynch

I am getting this error which is weird because this formula

COUNT(DISTINCT DriverID) fixed (BY MPP_TERMINAL_NAME)

works on its own but when I put it into this formula I get this error.

(SUM(CASE WHEN AVG(Revenue) >= 1000 THEN 1 ELSE 0 END) OVER (PARTITION BY DriverID))
/
COUNT(DISTINCT DriverID) Fixed (BY MPP_TERMINAL_NAME)

An issue has occurred during processing. We are unable to complete the request at this time - 'undefined'

Manasi_Panov · February 27

Hello @ColinHaze,

Handle cases where the denominator may be zero by using the NULLIF() function.

ColinHaze · February 27

Manasi_Panov

Still getting the undefined error?

Manasi_Panov · February 27

Hello @ColinHaze,

As a reference, I'm sending the info regarding FIXED function:

https://domo-support.domo.com/s/article/4408174643607?language=en_US

You can see from the link that FIXED function requires double parentheses. Could you please also try this:

SUM(COUNT(DISTINCT DriverID) Fixed (BYMPP_TERMINAL_NAME))

Beast Mode

Beast Mode

Using Partition by with Count

Welcome!

Answers

Welcome!

Welcome!

Quick Links

Categories