What's wrong with my Beast Mode Calculation?

Reply
Yellow Belt

What's wrong with my Beast Mode Calculation?

When I tried to show the results from the queries below I get this error, "An issue has occurred during processing. We are unable to complete the request at this time"

AVG(sum(distinct`Users`)/ `Content ID`)

 

AVG(SUM(DISTINCT `Users`) over (order by `Content ID`))

 

I want the average of users over content id, but the results won't show. 

 


Accepted Solutions
Black Belt

... this is bad sql.  while you can 'make it work'.  it will be prone to invisible errors.

 

you can't SUM DISTINCT values. ... or at least... you shouldn't.  you can COUNT(DISTINCT) values.  I assume you're using sum(distinct) b/c you have multiple rows with the same value repeated.  your hope is that if you sum(distinct) you'll remove duplicates.  but if you have the same number of users for one content ID you'd get the 'wrong value'.

 

to get this to work, you MUST have content ID on an axis or in the order by clause.  once you do that, you probably won't get the desired result b/c you'll have the sum( users) for each content id.  which will be ONE VALUE.  so when you then try to take the average of one value, you'll get the same number.

 

I go into it in depth here.  https://www.youtube.com/watch?v=eifSYZIcPzg

 

but the two major action items are, restructure your data so you're not trying to do a sum(distinct) and 2 if you need to know the average across content_ID you may have to preaggregate your data before pulling it into analyzer.


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"

View solution in original post

Original Domo post:https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Calculate-average-time-to-accumulate-to-a-value/m-p/50668// more windowed function videos...

All Replies
Black Belt

... this is bad sql.  while you can 'make it work'.  it will be prone to invisible errors.

 

you can't SUM DISTINCT values. ... or at least... you shouldn't.  you can COUNT(DISTINCT) values.  I assume you're using sum(distinct) b/c you have multiple rows with the same value repeated.  your hope is that if you sum(distinct) you'll remove duplicates.  but if you have the same number of users for one content ID you'd get the 'wrong value'.

 

to get this to work, you MUST have content ID on an axis or in the order by clause.  once you do that, you probably won't get the desired result b/c you'll have the sum( users) for each content id.  which will be ONE VALUE.  so when you then try to take the average of one value, you'll get the same number.

 

I go into it in depth here.  https://www.youtube.com/watch?v=eifSYZIcPzg

 

but the two major action items are, restructure your data so you're not trying to do a sum(distinct) and 2 if you need to know the average across content_ID you may have to preaggregate your data before pulling it into analyzer.


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"

View solution in original post

Original Domo post:https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/Calculate-average-time-to-accumulate-to-a-value/m-p/50668// more windowed function videos...
Yellow Belt

Ahhhh I see. Thanks so much for your help, I really appreciate it!

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!