What's wrong with my Beast Mode Calculation?

leeloo_dallas

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. 

 

jaeW_at_Onyx

... 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.

jaeW_at_Onyx

... 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.

leeloo_dallas

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