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.
I hope my question is clear.
Thank you for the link to the example.
Tried it quickly but was not able to get it. Will try again later.
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?
SumIf([Subtotal], in([Created At], "Current Week"))
SumIf([Subtotal], in([Created At], "Previous Week"))
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.
SumIf([Subtotal], interval([Created At], 0, "week")) and then
Interval has some quirks:
https://github.com/metabase/metabase/issues/12457 - upvote by clicking on the first post
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.
SumIf([Hours Column], interval([Booking Start], 0, "week"))
SumIf([Hours Column], interval([Booking Start], -1, "week"))
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.
@pvanthony Thank you for sharing full examples of what you did!