Hi all,
I have a Google Sheets data source and Column A lists the week numbers from 1 to 52. Column B just has a value to each week. I need to create a calculated field dimension so I can order the chart by month.
What formula can I use to accomplish this?
Tried to find a solution via Google Search and Chatgpt but none came with a working solution.
If you want to order by month, that would already happen if you order by week. It’s chronologically ordered already.
But it sounds like you might want to group by month instead? If that’s the case, you can add a calculated field for Month either in your Sheet directly or in Looker Studio.
If you choose to do this within Looker Studio, here is a calculated field that should work:
MONTH(DATETIME_ADD(DATE "2024-01-01", INTERVAL [your week number dimension] WEEK))
This basically adds your week number dimension to a dummy date, january 1 2024, and then gets the resulting month from the new date. It’s effectively pulling the month out of a week number.
Hi Sam8,
Thank you for responding to my thread even after so many weeks.
I was wondering could you give an example by what you mean with “your week number dimension”? of the calc field?
If my columns look like this:
01 100
02 051
03 923
04 838
So, that the chart that I will use sees 01-04 as month January.
Thank you in advance
Yup, you’ve got the right idea.
I am assuming that you’ve connected this sheet as a data source, and so you should have the week number available as a dimension. Then you can create a new calculated field with the syntax above. When writing a calculated field, you can reference dimensions from your data source, which is when you’ll write in the name of the column from your sheet.
Great and thank you for the formula!