HELP with weekly visualization

Hello everyone, need some help with visualization setup. So the problem is that I need to make one with weekly registrations.
Made a query in mysql:
SELECT DATE_FORMAT(created_at,‘%M %Y’) , COUNT(id)
FROM users
GROUP BY WEEK(created_at)
ORDER BY WEEK(created_at)

Table looks right:
3e151b4a-bc55-40c5-887a-0c3e92f40638

But when I create a visualization it groups all by month

I don’t understand if the error is in query or in visualization.
In ideal it must be column X that reports month/year with 4 or 5 columns (weeks) and column Y reports numbers or registrations.
Something like that

Thanks for your question.

The first issue is that your query returns multiple rows for each month:in your screenshot there are five samples for October 2021. Redash expects one row per x-axis sample. If it sees more records, it silently adds them together. Which is why you see monthly bars instead of weekly.

The second issue is that your date format (%M %Y) won’t sort correctly. Which is why October 2021 appears before September 2021. I’d recommend using a query like this:

SELECT
  date(DATE_ADD(created_at, INTERVAL(1-DAYOFWEEK(created_at)) DAY)) week,
  COUNT(id) user_count
FROM users
GROUP BY 1
ORDER BY 1 DESC
LIMIT 52

This returns a table like this:

|week|user_cnt|
|—|—|—|
|2021-04-26 00:00|592|
|2021-04-19 00:00|499|
|2021-04-12 00:00|487|
|2021-04-05 00:00|602|
|2021-03-29 00:00|484|
|2021-03-22 00:00|533|
|2021-03-15 00:00|535|
|2021-03-08 00:00|579|
|2021-03-01 00:00|636|
|2021-02-22 00:00|662|

And makes a visualisation like this:

1 Like

Dear jesse,
Thanks for quick answer. I’m sorry, I just started to study sql, so I don’t understand first part of query:

DATE_ADD(curdate(), INTERVAL(1-DAYOFWEEK(curdate())) DAY) week

If I change “created_at” with “curdate” nothing happens. If I start query as you wrote I get just a date 21/10/21 and 41 users

Sorry for the confusion. I wrote my example using curdate() because I was using sample data.

I updated the example to use your column name.

Regarding your other question:

-- The following in MySQL

SELECT   DATE_ADD(created_at, INTERVAL(1-DAYOFWEEK(created_at)) DAY)

-- Imitates the following in postgres

SELECT date_trunc('week', created_at)
1 Like

Dear jesse,
Thanks for kind reply. I thought so, that I need to change created_at with curdate. But in my case it doesn’t group by week.

SELECT DATE_ADD(created_at, INTERVAL(1-DAYOFWEEK(created_at)) DAY) Week,
COUNT(id) Registrations
FROM users
GROUP BY 1
ORDER BY 1 DESC

I get that:

Thank you

Oh! I didn’t recall that your created_at field is a datetime field. Notice that all the dates in your table result are exactly seven days offset from one another, but they retain the time component of the original created_at field. You can use the date() function to strip that away.

I updated my example.

-- Wrap in a date() call to cast to a date *without* the time component
SELECT   date(DATE_ADD(created_at, INTERVAL(1-DAYOFWEEK(created_at)) DAY))
1 Like

Everything works! Thank you so much. You’re a genius. If you don’t mind, I will ask the last question, how to make a month and a year on the X axis, as on your chart. I tried to change the chart settings by specifying MMMM YYYY in data labels-Date/Time Values Format and input to use datetime in X axis instead of auto detect, but it still comes out as in the picture below

The axis is formatted automatically. You can’t change it manually without modifying your query. But you’d have a problem either way because your data is more precise than by-month. At minimum you would need to show by Month + Week.

But your chart we see month/year, not month/day as on mine.

image

That is only because mine is zoomed out. I if I zoom closer like you do on your chart, it will show the dates instead :eyes:

Because:

1 Like

Ok, now all is clear. Thank you very much!!!