Using Partition by with Count

ColinHaze
ColinHaze Member
in Beast Mode

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)

Answers

  • Craig_Lynch
    Craig_Lynch Contributor

    Have you tried using FIXED (BY MPP_TERMINAL_NAME) ?

  • ColinHaze
    ColinHaze Member

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

    Hello @ColinHaze,

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

    If you found this post helpful, please use πŸ’‘/πŸ’–/πŸ‘/😊 below! If it solved your problem, don't forget to accept the answer.

  • ColinHaze
    ColinHaze Member

    Manasi_Panov

    Still getting the undefined error?

  • Manasi_Panov
    Manasi_Panov Coach
    edited 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))

    If you found this post helpful, please use πŸ’‘/πŸ’–/πŸ‘/😊 below! If it solved your problem, don't forget to accept the answer.

Categories