Avg Per Hour

leeloo_dallas
leeloo_dallas Member
in Charting

I'm trying to use the analyzer tool to get the average number of posts per hour.

avg(COUNT(DISTINCT `Permalink URL`) over(partition by HOUR(`Created Time`)))

I want to be able to use the following graph where the series is the average.

For some reason, when I run the query, nothing comes up on the graph and it turns blank stating an error, which makes me wonder if my query is incorrect.

Capture.JPG


Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    @user052846 when a result is blank, that can often mean one of your fields has blank values. Are both of the fields populated in every row? If not, I would add some additional logic to handle that.

    Another troubleshooting step is to break apart your calculation and see if you are getting what you anticipate. Try just doing a COUNT(DISTINCT ) and see if that returns expected results. Try without anything in your over clause. Try looking at it in a table card.

    Hopefully, breaking it apart will help you identify what might need to change.

    **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.
  • leeloo_dallas
    leeloo_dallas Member

    Actually I already have a beastmode for COUNT(DISTINCT `Permalink URL`) and I get values returned but when I do the following:

    avg(COUNT(DISTINCT `Permalink URL`) returns blank

    avg(COUNT(`Permalink URL`) also returns blank

    There are no empty values in the permalink column.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Does avg(COUNT(DISTINCT `Permalink URL`) OVER() work? I tested AVG(COUNT(DISTINCT field)) in my instance and that failed until I added the OVER() clause and then it returned a result. If that works, then it seems like the issue is narrowed down to the PARTITION BY statement.

    Also, I'm assuming yes since you are writing a statement like this, but do you have the window function for beast modes enabled in your instance? It isn't on by default and you have to request it through your CSM. Thought I would make sure.

    **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.
  • leeloo_dallas
    leeloo_dallas Member

    I got a result back, but the average is the same for every hour.

    What I would want is a secondary line for the average number of posts.

    Which makes me wonder if we also need to group by hour, but I don't know how to do that.

    image.png


  • leeloo_dallas
    leeloo_dallas Member

    I think I got it to work using this  avg(COUNT(DISTINCT `Permalink URL`)) OVER(partition by concat(HOUR(`Created Time`)))

    But it ended up looking like so...

    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach

    Try changing your Hour beast mode to be the exact same as your partition. You might also want to check the date filter under previous month and make sure it isn't grouping by something that would throw it off.

    **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.
  • leeloo_dallas
    leeloo_dallas Member

    The weird part is I used the same exact beast mode as in my partition by clicking on it.

    avg(COUNT(DISTINCT `Permalink URL`)) OVER(partition by concat(HOUR(`Created Time`)))

    I wonder what's wrong with it.

    My date is also using Created Time.

    So there's nothing wrong there.

    I'm pretty sure it's the function.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Technically, window functions in beast modes aren't totally supported, which is why they aren't enabled by default. Considering you have 3 questions posted in the Dojo and they are all centered around window functions not working beast modes, I think you should strongly consider building an ETL or two that gets the data that you are looking for and then you can build a very straightforward card. Using the Group By and Rank & Window tiles should allow you to get the totals you are looking for in each of your questions.

    **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.
  • leeloo_dallas
    leeloo_dallas Member
    edited April 2021

    Hey there, all my threads are for different projects and different datasets.

    Hence they're in different threads.

    So because I have other threads, you don't want to help anymore?

Categories