Tableau Tutorial - Practical LOD #12 - Relative Period Filtering (YTD, QTD, MTD, YoY) (Part 4)
Вставка
- Опубліковано 23 лип 2024
- The first 1,000 people to use this link will get a 1 month free trial of Skillshare: skl.sh/sqlbelle10211
This video is a step-by-step, comprehensive tutorial of how to do relative period filtering in Tableau, which is the 12th use case in Bethany Lyons' Top 15 LOD Expressions.
TOP 15 LOD Expressions White Paper:
www.tableau.com/about/blog/LO...
This video discusses
- how to do Year over Year (YoY) Year-to-Date (YTD), Quarter-to-Date (QTD), Month-to-Date
- effect of leap year in calculations
#Correction #Sorry : Leap Year Calculation Correction
• #Sorry - #Correction o...
There are 2 parts to this video
The first part we will go through and re-create the exact example in the white paper.
The second part is an expanded discussion on relative period filtering, and we will create that is more dynamic and flexible.
This video incorporates many Tableau concepts and functionalities
- FIXED LOD (Level of Detail) Expressions
- Table Calculations (Running Total)
- Secondary Table Calculations
- Specific Dimensions
- Basic Calculations (math, IF/ELSE, CASE)
- Parameters
- Highlight Action
- Custom Dates
- Date Functions (DATE, DATEPART, MAKEDATE, DATETRUNC)
Sections:
0:00 - Intro
7:12 - Using dayofyear for year-to-date
10:30 - Secondary table calculations
15:12 - Part 2: updated version with parameters
16:53 - Using normalized date axis
25:03 - Using DATETRUNC to calculate relative period (YTD, QTD, MTD)
34:19 - How to handle leap years in calculation - Наука та технологія
Those are the best video I've found on Tableau LOD expressions. Thank you so much! Everything is explained in such a clear and thorough way and the examples are most useful. I've watched quite a number of videos so far but only after yours I've got a clear understanding of how LOD really works.
Worth the wait! You did it again with a great comeback :-)
Watched the whole video while exercising on the Stair Master at the gym. Didn't see 40 minutes go that fast. I will have to watch it again for sure.
Thank you!
Thank you Abderrahim! I sincerely appreciate your support and your every thoughtful comment. I take your suggestions to hear, and I am happy to hear you find the videos helpful. I hope you and your family are all doing well.
You have an incredibly digestible way of presenting concepts and solutions, thank you 🙏
Thank you!
Impeccable delivery, as always. We're lucky to have you!
Thank you Mohammed!
A very imp and practical use case is discussed, great work. Also thanks for highlighting the corrections in shorts :)
Always looking forward to your learnings!
Thank you Shashank!
You remind me of my favourite teacher in school , friendly lecturer at college and the best professor in my university. Simple , straight and superb explanation . Thank you Belle
Thank you Chanu :)
Hi, the way you explain is so good, thanks a ton.
Thank you Krishna!
You just explained everything very well.. learnt so much things! Thank you!!
Thank you Chetan!
ञ़पर
ऑॉॉॉॉॉॉ
Amazing video!!! Thank you so much!!
Awesome lessons as always. Thank you.
Thank you Guiliko!
Thanks for your effort , it is very informative and useful.
Thank you Sachin!
You are a great teacher!!
Thank you Adriana! Happy New Year!
Thanks a lot Belle !! You are the best !! Would love to have a Q and A session !!
Thank you Akshay :)
Thank you 🦋
Amazing thank you so much!!!!
Thanks sqlbelle
very informative, thank you
Thank you Michal :)
Beautiful , thanks 🙏
Thank you Omar!
You are the best!
Thank you Madhu!
So detail video thank for sharing
Thank you Hanmath
Excellent, thank you. Regards.
Thank you Paulo!
Very useful 👍 great job
Thank you Chris!
Very helpful tutorial.. 👍👍
Thank you Sayak!
Great, really appreciate!!!!
Thank you Mr Stark :)
Very useful... thank you
Thank you Angelina
You are just awesome
Thank you Farrukh!
Great video. Please make a video about how to calculate YoY,QoQ,MoM,WoW when we are dealing with an aggregated field like profit ratio using LODs instead of a table calc.
Thank you!
Thanks!
Thank you so much Anthony!
Hi @sqlbelle, not related to the video, but... I have many tables and it has a column name "Client". The problem is I need to create a filter wherein ALL clients will be on that so whenever I filtered the dashboard, it will filter all tables. Not just depend the filter coming from one table only. Haven't tried that yet though, but I might need to get into that later...
Nice one really nice 👍 leap year issue I will not address as it has been corrected anyway🙂👍 thanks for posting 😃
Thank you Peter!
Very well explained! Thank you so much, incredibly helpful However, I coudn't get MAKEDATE function to work with a live connection. Is there a way around it?
I feel like the Leap Year Day of Year condition if simply using the +1, would it also change the day of year for Jan? (in this example, 2018, 2019 and 2021, the first day of year would be 2 whereas the first day of year of 2020 would be 1)
You are absolutely right!!!! I can't believe I missed that. I had another version where I had another condition where I considered the month but somehow I missed it in the edit.
IE
if leap year and month > 2
....
Thank you Yifan, you are right. hmm now I need to figure out how to correct this video.
Hello Belle, I am having a problem, when I am trying to create "Normalized Order date" tableau is showing a error = Cannot mix aggregate and non-aggregate arguments with this function.
I understand it, because I choose YEAR from MAX RECORD DATE it is a aggregate value, and Month and Day from normal Order date they are non-aggregate arguments. Do you know how to fix it?
I really appreciated your videos.
Thank you so much.
Hello, can you plase share the link to the workbook? Thanks in advance, @sqlbelle !
Great video - just wondering how I would incorporate the leap year adjustment into the YTD, QTD, MTD example that you provided? Thanks
Also - how could we compare say 3Q 2018 to 3Q 2017, or 2Q 2017 to 2Q 2016 given that the quarter to date is based on the most recent records that might not fall in those quarters? Thanks!
thank uu for your amazing video, but after follow your instructions, why my choose duration can't move from YtD to QtD and MtD??
Hi, I am 2 years late to this amazing tutorial, but hopefully you notice my question.
I followed along with my own datasets, and I found that if `quick table calculation - different from` contains NULL, the bar/area chart below will not show.
for instance,
2017 || 10 | 20 | | |
2018 || | 10 | 30 | |
The table calculation will results in some weird empty area.
Is it possible that I make NULL values into 0s?
Much appreciated!
by creating the calculated field(within date range ,time - 9:33),error is popping up - "cannot mix aggregate and non-aggregate arguments with this function. why and what to do?kindly help, btw i'm using the superstore dataset which is updated(as it showing year from 2020 to 2023).
PLease create a video on how to calculate YoY,QoQ,MoM,WoW, pleaseeeeeeee
Will do! :)
Also for part 2. At the very start when you were solving the issue with having sperate lines for each year (creation of the normlaised order date) ? Why couldnt we just place week number on the axis like we did in part 1 ? Was thias just hsowing us another way of doing the samething ?
Edit
I feel like you did it a different way in part 2 becuase we will be using parameters ?
Is there a workaround for MAKEDATE function for a "Live" database? MAKEDATE function isn't available for live connections.
As always, you delivered the best. I have one thing can't solve on my own, when I dragged the year to the color panel, the filter is scale, not a color button, I don't know how to fix it.
Hi Sandra! You can change the year to a blue pill (Discrete), and it will give you access to the discrete colors (not gradient)
I have a question about the Leap year issue. From your Field Calculation, for Non-leap Year, we add 1 to the DayOfYear. But the discrepancy only starts from 02/29. Eventhough adding 1 will create the discrepancy from 01/01 to 02/29, because it is now off by 1?
Wonderful presentation - clear yet concise.
When I dragged the second instance of the same measure (my own workbook) to the table nothing happened. Any idea why?
You will need to ensure that these are two different measures first. For example, make sure the first instance has a table calculation first (hence transforming it into a different measure) before dragging a second instance of that measure.
Can't find the link to download the Workbook. Can you post it here. Thanks.
Thanks.
TOP 15 LOD Expressions White Paper:
www.tableau.com/about/blog/LOD-expressions
Would you be able to make the videos for the rest 3 LOD? 13-15
Thank you Sandra, I will!
Hi, I wanna ask how to calculate Ytd growth? for example (stock price in 13/02/2022-stock price in 30/12/2021)/stock price in 30/12/2021)*100. I confused what's the formula to calculate it... I hope you can help me :)
Thank you Yanji - there is a Quick Tableau Calculation for YoY and YTD .. you will need to make sure you have a discrete date part fields in your view (for example, Year, Quarter and Month), and the YTD and YoY options should be enabled
hmmm. Whats the reason for making max record date fixed ? how would it change if we only aggregated it to max order date without fixing it ? Thanks !
Thanks. If we use the "regular" aggregation, it means that the field will respond to the level of detail of the view - for example if you add dimensions, or add filters etc - all those will influence the "max date". So a "max date" can become "max date" per category, per subcategory, per date range selected etc.
@@sqlbelle thanks for replying. I learned by trying to answer my own question, and found out. I guess I'm à real « Learn by doing » person :) Merci.
@@agger0083 That's great, thank you :)
Why do you use the LOD max order date instead of just doing a normal max order date?
It depends on whether you want an "absolute" max date. A "regular" aggregated max date will be influenced/affected by the dimensions in a canvas, so there is no guarantee it wont change.
@sqlbelle Can you please create a dashboard containing bins, sets, groups, parameters ?
How to show To date period differences by Category/Subcategory?
when I drop in categories it only shows the total overall difference per line not by individual category/sub-category.
I followed this video: ua-cam.com/video/QSIUdjfu8B0/v-deo.html
Appreciate any help on this as not sure how to fix it?
Note to self in 79 years: remember that leap years are ignored if the year is divisible by 100, so write a special case for that in your logic.
Great point.
Very nice teaching mam..... But you didn't practiced LOD 13,14,15 😀..... Please give us the explanation 👍
Thanks!
Thank you jnhay, much appreciated!