Group by Day-of-week

[will.b (Will Bryant)]

When we have a date or date-time field, Holistics lets us specify the granularity eg. go down to week or month resolution.

Our business has a strong weekly cycle and so it is very common for us to want to group the data by day-of-week ie. Monday through Sunday, across all days in the filtered date range. For example we might be looking at 12 weeks’ data, but we want to don’t want to see 84 rows/points in the chart, just 7.

So we would love a “by weekday” or “by day of week” option in the drop-down on date/datetime fields. Similarly “by hour of day” would be useful.

The documented but unimplemented week_day() function would be a start as then we could do it in a business calculation, but that’s unimplemented anyway, and returning a number rather than a string is pretty user-unfriendly, so it’d be a bit of work to make it usable.

[Khai_To]

Hi @will.b,

For now, we don’t support an option to add the day of the week. I will add this to our backlog for future improvement.
As a workaround, you could do some data transformation to get the day of the week as shown in the image below

In the modeling layer, navigate to your model that has the date field used in the dataset. And then:

• First, get the Day number from the original day extract(isodow from {{ #THIS.order_created_at }}) - 1 by creating new Custom Dimension.
• Second, get the Day name (Mon, Tue, Wed,…) from the original day to_char({{ #THIS.order_created_at }}, 'Day') by creating new Custom Dimension.
• After that, you need to concatenate 2 fields together: concat({{ #THIS.day_number }}::text,'-',{{ #THIS.order_day }} ) and called days_of_week.
• Finally, in the Dataset, you will need to use the days_of_week field in the X-Axis.

Do note that the syntax above is for Postgres only, you will need to adjust your syntax accordingly (based on your company’s database).

Do let me know if you need further support.

[will.b (Will Bryant)]

Thanks Khai. That sounds like an OK workaround for the short term.

I guess it’d be nice if we could sort by a numeric field and then use a separate display expression to string-ify it, to avoid needing to put in the ordinal prefixes. But a proper feature for this will be even better.

Good to hear it’s on your backlog now - thanks. For comparison, here’s the options Metabase has - I think “Hour of day”, “Day of week”, “Day of Month”, and “Month of year” are the ones I would use personally, but probably good to have them all:

[Alex_H]

Absolutely - Hour of Day, and Day of Week would be really useful!

The metabase example is definitely a good step forward.

[huy (Huy Nguyen)]

Reopened by @Alex_H at: More variety of date aggregations (Day of Week, Day of Month, Min/Max Date, Hour of Day, etc)