Average

I have created a Card for volume by hour.
Now the department wants a card Average volume by hour.
COUNT(`Case Number`) OVER (PARTITION BY HOUR(`Created Date`))
I'm not sure if I'm doing it right as it's creating duplicate Hours and Volume.
I have done it in ETL and getting the result but then I cant do filters as It's a group by function.
I want to do it in the beast mode so I can apply the filters for the drill own.
Anyone has any idea?
Best Answer
-
Sadly this isn't possible as you're wanting to aggregate (average) an aggregate (count) which isn't possible. The only way to get close would be to pre-aggreagate your data in an ETL and then take the average in your card but that's not what you're wanting since you want filters to be applied.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Is there anything in your sorting properties? That is often a common cause as to why it is not grouping as expected.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
I was sorting it by hour and if you don't then that's how it's appear.
0 -
@GrantSmith is the king of window functions. He will be able to tell you what to do.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
Thanks @MarkSnodgrass , Looking forward to meet you all in March. Hope you are coming :)
0 -
Hi @Hiraayub when you say you want a the average volume by hour, do you mean the average cases within, say hour 1 and hour 2, across multiple days? If so, try changing your beast mode to partition by day instead of by hour, since your data is already separated by hour in the x-axis.
COUNT(`Case Number`) OVER (PARTITION BY DATE(`Created Date`))
0 -
Thanks @MichelleH, I did try that before but the result is the same.
0 -
@GrantSmith I knew that but I was hoping there was another way to work around it.
Thank you.
0
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 38 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 258 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 104 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有