1

Hi everyone!
I’m looking for good solution of the following problem:
I have daily data for sales and I would like to have visualisation in which I can switch between displaying sum(sales) in different time periods (daily, weekly, monthly).
In ideal version I have Date-Range parameter where I can chose the range of dates between which I can see the values and dropdown parameter where I choose time periods (day/week/month).

As an example: it’s 21st of September today and I’ve chosen this month in Date-Range parameter. When on day/week/month parameter I choose:

  • “day”, I’ll see 21 bars, one for each day
  • “week”, I’ll see 3 bars, one for each Monday this month
  • “month”, I’ll see only 1 bar, from the beginning of this month

Much appreciate your hep!

1 Like
2

If I’d to do it, I’d use a dropdown with the wanted value for the group by

  • Day
  • Week
  • Month

then use this value in my
GROUP BY DATEPART({{my_dropdown}}, date)
(sql)

By changing the value of the dropdown it will refresh the graph with the wanted precision

You can keep your datepicker to select the period you want;

In some of my dashboards, I’m even computing the “precision” according to the period selected.

1 Like
3

Thank you @xavier-d this is very interesting direction.
This solution works almost as what I need. There is one problem I found with selecting “day” and looking for the whole year time-span:
as DATEPART({{my_dropdown}}, date) return day as int type, the graph is showing 31 bars (one for each number for day in date), not 1 bar for each day (on September 21st it should be 264 bars).

4

Hey, I created a bit more complicated solution for my purposes.

  1. Start using Python Data source. It’s like +10000 features to readsh.
  2. Create dropdown with group by types
  • Day
  • Week
  • Month
  1. Write simple if-else. Smth like
if '{{Group By}}' == 'day':
    grouper_value = 'toDate(date) as grouper'
elif '{{Group By}}' == 'month':
   grouper_value = 'toYearMonth(date) as grouper'
  1. Your query string no will look like this:
SELECT {}, 
         other values
FROM table
GROUP BY grouper
'''.format( grouper_value)
5

@TOc To address the issue with 31 bars you probably don’t want the datepart function in this case, you want the actual date. The logic can get pretty complex to switch between the two. In order to simplify this you might consider creating a calendar dimension table with one row for every date and capturing all the relevant attributes for these dates. There are plenty of examples on the web as to how this might look. But you might be interested in something like:

dt | year | year_month | year_week
2020-12-31 | 2020 | '2020-12' | '2020-53'
2021-01-01 | 2021 | '2021-01' | '2020-53'

Then you could join to this table in your query and just specify the column names (dt, year_month, year_week) in the filter you want using the dropdown that @xavier-d suggested. The calendar dimension table would also allow you to better align what defines a week/month with your business (does a week start on Monday/Sunday, are we looking for fiscal months instead of calendar months?). This table would also be re-usable for a lot of other queries as well.

1 Like
6

Thanks @ptr106 - this is the solution I want to try today - it seems to be easiest one to create and replicate if needed.

1 Like
7

A rather easy way a colleague showed me:

SELECT 
    EXTRACT(YEAR FROM creation_time) AS YEAR,
    CASE
        WHEN "{{ period }}" = "DAY" THEN DATE(creation_time)
        ELSE EXTRACT({{ period }} FROM creation_time)
    END AS period,
    MIN(DATE(creation_time)) AS Date,
    COUNT(*) AS value

GROUP BY period, year
ORDER BY Date```
1 Like