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. 

 

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    ... 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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    ... 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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Ahhhh I see. Thanks so much for your help, I really appreciate it!