How to compare three weeks of booking data?

Get Help
1

Currently I am able to compare two weeks of booking data.

For example, create a question for current week. Then create another question for previous week. The questions are created using something like booking_start previous 1 week and booking_start current week. This is done in the question edit page. Then the data is grouped by weekday.

Then in the dashboard, both the questions are merged.

Now I would like to create a bar graph with three weeks data. So it will be current, previous and previous previous week. How to do the previous previous week question?

The graph is dynamic, so every week it will be different. Just wondering how to do it without actually typing in the dates.

Here is the screen shot of the current and previous week question in bar graph.

Screenshot%20from%202021-06-15%2016-28-50
Screenshot%20from%202021-06-15%2016-28-501818×556 15.6 KB

I hope my question is clear.

2

Hi @pvanthony
Have you looked at this guide?
https://www.metabase.com/learn/building-analytics/notebook-editor/time-series-comparisons.html

3

Thank you for the link to the example.
Tried it quickly but was not able to get it. Will try again later.

4

I looked at the documentation and it is great when the dates are fixed.

Is there a way to create three dynamic summaries like the following?

summary 01: SumIf([Subtotal], in([Created At], "Current Week"))
summary 02: SumIf([Subtotal], in([Created At], "Previous Week"))
summary 03: SumIf([Subtotal], in([Created At], "Previous 2 Week"))

I hope to make it dynamic so that the bar graph is always updating and live.

Need help and advice.

5

Hi @pvanthony
Yes with interval function:
SumIf([Subtotal], interval([Created At], 0, "week")) and then -1 and -2
https://www.metabase.com/docs/latest/users-guide/expressions.html

Interval has some quirks:
https://github.com/metabase/metabase/issues/12457 - upvote by clicking :+1: on the first post

6

Yes! Yes! Yes! This is great! And it works! Thank you very much for sharing this. A real life saver.

Here is what was done for my use case.

Under the summaries, created three rules.

  1. current week
    SumIf([Hours Column], interval([Booking Start], 0, "week"))

  2. previous week
    SumIf([Hours Column], interval([Booking Start], -1, "week"))

  3. previous previous week
    SumIf([Hours Column], interval([Booking Start], -2, "week")) - SumIf([Hours Column], interval([Booking Start], -1, "week"))

Then did an order by like
Booking Start: Day of Week

With this way everything appears in one question and graph. No need to merge two question together.

This is so powerful and helpful!

Thank you again for sharing the solution.

1 Like
7

@pvanthony Thank you for sharing full examples of what you did!