Filtering data before aggregation in card view

Jones01

HI,

I am very new to domo and need some help to see if this is possible.

I have hourly data for 14 days. 7th Jan 2019 to 20th Jan 2019.

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

The problem is I want the user to be be able to filter the hours that make up the aggregation.

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