Return AVG DayName

Hey I was wondering if there is a beastmode windows function that would allow me to return the avg dayname from the average of x?

 

I have data that includes a domain, a column for day name(made via beastmode), and an avg of impressions).

 

Any ideas? I thought the following would work if I added concat in front of it but I keep getting 0 as a returned response. 

avg(distinct DAYNAME(date))

Best Answer

  • MarkSnodgrass
    Answer ✓

    Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:

    CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
    END
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    Answer ✓

    Average doesn't work for words, but you could use the DAYOFWEEK function to get the numerical day, average that and then convert that to the day name. Would look something like this:

    CASE ROUND(AVG(DAYOFWEEK(`date`)),0) WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
    END
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Oh my gosh I didn't even think to use DAYOFWEEK.

     

    It works well too!

     

    I was curious, do you think this returns there's a way to FLOOR or CEILING to get the lowest avg and highest avg to DAYOFWEEK?

  • Kind of like this?
    (CASE ROUND(AVG(FLOOR(DAYOFWEEK(`date`))),0) WHEN 1 THEN 'Sunday'
    WHEN 2 THEN 'Monday'
    WHEN 3 THEN 'Tuesday'
    WHEN 4 THEN 'Wednesday'
    WHEN 5 THEN 'Thursday'
    WHEN 6 THEN 'Friday'
    WHEN 7 THEN 'Saturday'
    END)

  • Yes, I thought about including floor or ceiling in my reply, but wasn't sure which way you would want to go. I would do some testing and just see what works best for your situation.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Will do! Thanks so much for your help! 

     

    Also, did I apply that correctly? Like the FLOOR to the query?

  • Actually FLOOR would replace ROUND and it would be outside the average function. Like this:

     

     FLOOR(AVG(DAYOFWEEK(`date`)))
    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Here's link describing the floor and ceiling functions.

    https://www.mathsisfun.com/sets/function-floor-ceiling.html

     

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ... oof are you trying to figure out on which day of the week people tend to visit your site?

    if yes, then you should definitely bake day of week into your dataset via ETL, different implementations of dayofweek will shift Monday from 1 to 0, and that's not the sort of thing you want to guess about.

     

    meanwhile, it's interesting you mention avg. number of impressions, b/c depending on the granularity of your dataset (what each row of data represents), the avg. number of impressions may differ from the avg day of week in your dataset.  

     

    make sure to be sure the math you're calculating is answering the business question!

    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"