Domo Beastmode Window Function

brady_price

Hello!

I'm trying to get a window function to work while creating a beastmode so it can be dynamic for my users. I'm trying to show the unique number of users partitioned by rubric.

So, my beastmode is COUNT(DISTINCT `user_id)) over (partition by `rubric_id`). This works when I just show the beastmode:

However, when I then put in the different standards for that rubric (you can have multiple standards in a rubric) it breaks on me and shows me this:

What I really want is for it to show me just one standard (currently it repeats all of my options twice) and for it to have the original 5,741 number for each row. Any insights into what I'm doing wrong and how I can get where I am trying to go?

GrantSmith

Hi @brady_price

Your beast mode looks incomplete - you have a trailing closing parenthesis. Are you doing anything else with your count like a SUM perhaps?

COUNT(DISTINCT `user_id)) over (partition by `rubric_id`)

The way Domo functions is that it will run your "query" twice. The first time with the beast mode to return a single value for each row in your dataset, then if you have any groupings or filters it'll query that resulting dataset again. In your case you're adding an additional column to your chart which will be group by so it will aggregate it after your window function has been done causing more records to appear than expected.

Have you tried wrapping your window function with a MAX like:

MAX(COUNT(DISTINCT `user_id)) over (partition by `rubric_id`)

jaeW_at_Onyx

you can't run count(distinct) in a window function and get the 'correct' results.

if you imagine it as a sql query.

SELECT
rubric,
count (distinct user_id)

FROM
Table

GROUP BY
rubric

this would give you expected results. and this is what a beast mode set up with just count(distinct) without the over clause.

when you add the over clause you must have nested aggregation if you run:

max(count( distinct userID)) over (partition by rubric)

then your count distinct executes with the GROUP BY clause in place, in your second example

select
max(result) over (paritition by rubric) ,
rubric,
standard_title,

from
( select count(distinct userid) as result , rubric, standard title from table )

GROUP BY
rubric
, standard_title

in terms of actual results, you get your count distinct by rubric and standard title. THEN domo takes the max result. that is obviously not a count distinct across all titles within that rubric which is i suspect what you want.

domo can't give you that using a beast mode. because after the internal query executes, you no longer have access to the user ids. they've already been aggregated.

brady_price

Hey @jaeW_at_Onyx and @GrantSmith. I appreciate you looking into this. Grant, I mistyped and had a trailing ) that I didn't mean to have. And I did try to do the max(count(distinct `user_id`)) over (partition by rubric) and it didn't work.

From what it sounds like Jae is that what I am wanting is not possible? Because I do want it to show me the distinct count of users for each rubric for each row. I don't want to group it by standard. I need a distinct count of users for that rubric on each row of the standard so it can be my denominator. Please let me know if there is another way that I can get the 5,741 (unique users for the specific rubric that I'm filtering) on each row of my standards within that rubric.

Thanks,

Brady

jaeW_at_Onyx

you could calculate the metric in a dataset view or magicETL before you get into analyzer but then your beast mode won't respond to page filters.

alternatively restructure your data.

set up your data such that you have one row per user per rubric. this way you can take the SUM of the denoinator instead of trying to do a count distinct.

here's a walkthrough:

https://www.youtube.com/watch?v=Xb4QgKYgaqg&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=73