Avg Per Hour

leeloo_dallas

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.

MarkSnodgrass

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

leeloo_dallas

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

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.

leeloo_dallas

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.

leeloo_dallas

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

MarkSnodgrass

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.

leeloo_dallas

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

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.

leeloo_dallas

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?