How I Calculate Weekly (WoW) Growth Rate in Data Studio Using Data Blend (Self Join Method Example)
Вставка
- Опубліковано 2 лип 2024
- Week-over-Week is critical to measure a business' success! In this tutorial, I'll explain how I managed to count weekly growth using sales data with "DATE" and "TOTAL ORDER" field in Google Data Studio (GDS) using Blended Data Solution.
You may use the same concept shown in this video for date / week / month (MoM) / quarter / year (YoY) comparison with some tweaks in the formulas.
Blend Data is extremely useful when you're dealing with more advanced calculation and visualisation needs. Here are some other applications:
D A T A B L E N D T U T O R I A L S 👇
⇨ Grouping Data using Aggregated Metrics: • DATA BLEND TUTORIAL: H...
⇨ Calculate Percentage: • How to Calculate Perce...
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐬𝐮𝐩𝐩𝐨𝐫𝐭 𝐦𝐨𝐫𝐞 𝐜𝐨𝐧𝐭𝐞𝐧𝐭 𝐥𝐢𝐤𝐞 𝐭𝐡𝐢𝐬 ?
👉 You can now buy me a coffee @ ko-fi.com/T6T25PD0A
👉 Subscribe to my channel @ ua-cam.com/users/Danalyser?s...
👉 Shop Danalyser Templates Collection
@ www.danalyser.com
#GoogleDataStudio #Danalyser
T I M E S T A M P
00:00 How the solution works ? Concept Explained
03:18 Step 1: Group Dates to Week Number (Data Blend)
05:15 Step 2: Self Join to Get Last Week's Amount
07:55 Step 3: Add Calculated Field to Count Percentage Change
09:05 Adding Percentage Change to Line / Bar Chart
10:45 Style Modification to Adjust Labels Scale
⭐️ NEW TO GOOGLE DATA STUDIO
Check out our FREE GDS Course (2022) here 👉 • FULL Google Data Studi...
We uses practical approach and covers almost everything you need to get started in Google Data Studio! Take the course now and shine ✨
F R E E B I E S
I used Canva to make graphics for my educational videos and thumbnails. If you want to try this amazing tool, you are welcome to use the link below!
Try Canva Pro FREE for 30 days: partner.canva.com/danalyser
[ This is an affiliate link. I receive a small commission if you decided to subscribe to the Canva plan after trying it out. There's no extra cost to you.]
--------------------------------------------------------------------------------------------------------------------------
A B O U T M E
👧🏻 The person behind this channel is a Malaysian-Chinese Data Analyst. Aspired to create content for non-technical background audiences to up their game in their careers or even for students to prepare for their future career at early stages.
Welcome back! We have reached 1000 subscribers milestone this week ! Thank you for all the support :)
SL
Congrats Danalayser. I am fascinated by how knowledgeable you are in GDS and how beautiful you are. I can't help to stare at you.😅
Thank you for this video, really really helpful and great useful tip indeed on how to do percentage growth in Looker!!!
You are awesome. Thanks for these insightful tutorials.
My pleasure! Thanks Chinmaya! :)
It was useful!! Thanks!!
Glad to hear that!
Great tutorial! I just noticed on 7:45, your Total Order on table 2 didn't match the Total Order on table 1 after you added Last Week's Order..for example on Week 25, total order became 45 from 38
WoW % video was very helpful.
is there a way to do same exercise without blending the data ?
or can you please help me with running sum and slicer after blending the data
Hello, what if we start our weekly analysis on Sunday instead of Monday? How will the formulas change?
Hi, is there a way that I can change the week to week start/week beginning?
Wow! you're awesome! you just save my job tonight! I was wonder how can I add a "breakdown dimension" with this self join method?
Hey Jesse you can add the dimension in the data blend dimension, and use it as the join key, on top of the existing join key mentioned in the video.
However, it does require the dimension to have value in both week - otherwise you might have missing number here and there. Hope the explanation is clear enough :)
Thanks for watching my videos :))
@@danalyser Great!! I will try it! Thanks so much!
@@JessePineapple1200 You're so welcome, hehe have a good day! 🥰
Thank you. tipped
Thank you so much for your generous support! ❤
Hey I loooooooooove your videos and thank you so much and please keep making them!! Do you know how to make the weekday starts from Sunday not Monday as default????
You're so sweet thank you so much!! Sorry for the late replies as I am not aware that this comment gets moved to "held for review" by UA-cam 😅
For your question, I've found a solution from GDS forum that you may adapt to: support.google.com/datastudio/thread/8643969/can-someone-give-me-the-formula-to-change-the-week-day-start-from-monday-to-sunday?hl=en
Thanks for the support again!
@@danalyser Thank you so much! Do you think it's possible to aggregate 4-5 weeks that start on Sunday to make it a customized month dimension?
@@niakwon2978 You're welcome! ❤ Yes, it's totally possible if we use CASE or IF function, I've covered it in one of my tutorial so I'd put the link below for your reference yea. It's about how to create a custom date filter, but you'll learn how to create the dimension using the calculated field for a few date ranges.
Hope this helps!
Custom Date Range Filter: ua-cam.com/video/YTlTKAUct2g/v-deo.html
mind blown... thank you @danalyser. Exactly what I was looking for ... can't wait to trying this out
Naicee! Please let me know if it works for you!! :)
it did work, you made my day, thank you! :)
@@fcortesjp 💛💛
Awesome. Wondering any HR analytic , especially for employee turnover
Would like to.calculate active headcount of selected date range filter , based on joined date and resigned date.
Thank you! Would love to cover HR analytics dashboard in my future videos, if you have any sample data / ideas for the analytics, feel free to share with me at my email or instagram: @danalyser8. Thanks alot!
Thank you
You're welcome Albert! :)
useful stuff! How do you get the week and month instead of a week by year?
Thank you John! Not sure if I get what you mean... any examples for the date format you mentioned?
Yes! For example is it possible break it out into week 1 - Jan 1 - 7, week 2 - Jan 8 to 16 etc. So in the table we can show it as Jan 1-7 2023, Jan 8 - 16 2023 as columns?
Hi, how do you display the dates in that particular week? Like the table at the left "25 Jul 2022 to 30 Jul 2022 (Week 30)" instead of just "202230"
Hello Ms.@danalyser,
I have the same question and how to solve it?
Finding a solution to this would be the cream on top to this great video.
I mail you didn't respond. Please share your mail in about where can share my queries with you thanks
Hi, I've checked and didn't receive any new email. Kindly check again.
so difficult.... can you explain easy............. please
Thank you so much for this! i wanted to ask if it is possible to this with month, i tried with DATETIME_ADD(Date, INTERVAL 1 MONTH) howeever it isnt working :(. Really appreciate if there's a solution for above
You can use MONTH() function instead of YEARWEEK() in the Step #1 if your dataset only contains one year with no repeating months.
Or you may use CONCAT(YEAR(date_field),"-", MONTH(date_field)) for the formula.
Hope this helps!
@@danalyser Thank you very much for this tutorial! Is kind of weird to have to do al this to have a WoW or MoM growth, but it works. I used this CONCAT(YEAR(date_field),"-", MONTH(date_field)) formula to calculate de MoM but the problem is after that you can´t order ir by month because it recognice only the first digit, so the resulting order is january, october, november, december, february, march, april.... Do you have any clue of how to fix that?
Another question is Do you know how to calculate de grow of the week or month compared to the last year? Using Datetime_add 12 MONTH?
I really apreciate what you doing. Thank you very much again.
@@danalyser I'm having the same problem as @flekysaavedra
It cannot be arranged by time. Do you have any idea of how to fix that?
Many thanks
@@danalyser Concat will not join the date since it's text. This one should work DATETIME_ADD(Date,Interval 0 Month)