Can this be built with a card and beast mode without pre aggregating
HI,
As the title says can this be built without having to pre aggregate the data via a view or ETL.
I have hourly data for 14 days. 7th Jan 2019 to 20th Jan 2019. data set is much larger but as an example. So the data is datetime and a count figure.
e.g.
07/01/2019 01:00 6
07/01/2019 02:00 9
07/01/2019 03:00 10
07/01/2019 04:00 12
07/01/2019 05:00 15
...
...
08/01/2019 01:00 9
08/01/2019 02:00 33
08/01/2019 03:00 122
08/01/2019 04:00 11
08/01/2019 05:00 66
I need the average count for each day of the week.
So the average for a Monday, Tuesday etc.
I can do this in SQL and I can also do this using the group by in the domo views and ETL by aggregating the data to the day of the week and then using the average in the card builder.
The problem is I want the user to be be able to filter the hours that make up the aggregation on the fly within the card
e.g. just see between midday and 3pm etc.
Does anyone know how I can achieve this as once the group by is done I have lost the hours in the card analyser and using a window function within beast mode won't allow anything like
avg(sum(incount) OVER (partition by DATE(datetime), DAY(datetime))
Thanks
Answers
-
I'm not sure what chart type you are using, but I believe I was able to do this with three beastmodes. One to get the name of the day and another to capture the hour from your datetime field.
HOUR:
HOUR(`DateField`)
Week Day:
DAYNAME(`DateField`)
Avg Total Count:
AVG(SUM(`Count`)) FIXED (BY day(`DateField`))
You can then see the average total for any day of the week. You can also filter to only include certain hours or only values between certain dates.
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Hi,
Thanks for replying.
I didn't know the FIXED function was available yet. I have tried this and it seems to give different results to using.
SUM(`INCOUNT`)/COUNT(DISTINCT DATE(`DATETIME`))
I'm not quite sure why, I have a PIE chart grouped by DAYOFWEEKNAME and then I used your example.
Thanks
Paul
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 194 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive