x

Reply
White Belt

x

 
Black Belt

what is the shape of your data? what is the granularity? (one row per judge per year? one row per judge when they were made 'active?')

 

if it's one row per judge, then just do count(distinct judge_id) as a beast mode, and then Active Date on the axis.

 

if it's one row per year, then you want to filter where year(active_date) = year( transaction_date) and then do a count distinct.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Tags (1)
White Belt

x

Black Belt

my question about how the data is structured is important to the solution to this problem.  what is the granularity of the dataset.

 

a window function would get you halfway there

 

sum(sum(amount) over (order by year(date)) --

 

IF the granularity of the dataset was one row per judge.

but if the granularity of the row is one row per judge per year, or one row per judge per activity, you cannot use a window function because you cannot count distinct across partitions the way you'd like to.

 

sum(count(distinct judgeID)) over (order by year(date))

would return the wrong result b/c you'd count distinct by year and then sum, which would count judges that appeared in multiple years more than once.

 

and you can't use any other aggregation type count(count(distinct)) or any permutation of that b/c when you've aggregated to the year level, you lose context of the individual judges and you have an aggregate number like 5, 400 or 30.

 

IF you want to retain the granularity of one row per judge per year, then reshape your data such that you have one row per judge per year and measurement of 1 or 0 to indicate if they were active.  then you can just take a SUM across the board.

@GrantSmith 

 

 


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
White Belt

x

White Belt

i solved this myself. Thanks

Announcements
New & Improved Dojo: Get excited for a new feel and additional features in the online community.

Site will be down February 4-5th for maintenance.

Launch coming February 8th!