Tableau Tutorial - Practical LOD #12 - Relative Period Filtering (YTD, QTD, MTD, YoY) (Part 4)

Подписаться 120 тыс.

Просмотров 44 тыс.

50% 1

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

Наука

Опубликовано:

4 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 93

@SummerWasGone 28 дней назад

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.

@ExilesAnalysis Год назад

You have an incredibly digestible way of presenting concepts and solutions, thank you 🙏

@sqlbelle Год назад

Thank you!

@agermoune 2 года назад

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!

@sqlbelle 2 года назад

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.

@shashankworldpeace 2 года назад

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!

@sqlbelle 2 года назад

Thank you Shashank!

@mohammedaltantawi1545 2 года назад

Impeccable delivery, as always. We're lucky to have you!

@sqlbelle 2 года назад

Thank you Mohammed!

@chanuchan2765 2 года назад

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

@sqlbelle 2 года назад

Thank you Chanu :)

@krishnasharmacool Год назад

Hi, the way you explain is so good, thanks a ton.

@sqlbelle Год назад

Thank you Krishna!

@akshayravi1629 2 года назад

Thanks a lot Belle !! You are the best !! Would love to have a Q and A session !!

@sqlbelle 2 года назад

Thank you Akshay :)

@black920135 2 года назад

Amazing video!!! Thank you so much!!

@chetanms1270 2 года назад

You just explained everything very well.. learnt so much things! Thank you!!

@sqlbelle 2 года назад

Thank you Chetan!

@ailn-ug4jn 2 года назад

ञ़पर ऑॉॉॉॉॉॉ

@giuliko 2 года назад

Awesome lessons as always. Thank you.

@sqlbelle 2 года назад

Thank you Guiliko!

@SachinKumar-qo6ut 2 года назад

Thanks for your effort , it is very informative and useful.

@sqlbelle 2 года назад

Thank you Sachin!

@AdrianaPerez-ev5cj 2 года назад

You are a great teacher!!

@sqlbelle 2 года назад

Thank you Adriana! Happy New Year!

@yifanyu6554 2 года назад

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)

@sqlbelle 2 года назад

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.

@yvettecordero1813 Год назад

Amazing thank you so much!!!!

@cococnk388 Год назад

Thanks sqlbelle

@AliciaMarkoe Год назад

Thank you 🦋

@himachaudhary5531 Год назад

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?

@paulocarapinheiro8739 2 года назад

Excellent, thank you. Regards.

@sqlbelle 2 года назад

Thank you Paulo!

@user-re5uv2mm2h 2 года назад

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...

@michafabich9213 2 года назад

very informative, thank you

@sqlbelle 2 года назад

Thank you Michal :)

@tattwatripathy3619 2 года назад

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.

@sqlbelle Год назад

Thank you!

@madhugadodia5250 2 года назад

You are the best!

@sqlbelle 2 года назад

Thank you Madhu!

@MrOmaralamri 2 года назад

Beautiful , thanks 🙏

@sqlbelle 2 года назад

Thank you Omar!

@hanmanthkageru7870 2 года назад

So detail video thank for sharing

@sqlbelle 2 года назад

Thank you Hanmath

@MrStark-zy6cd 2 года назад

Great, really appreciate!!!!

@sqlbelle 2 года назад

Thank you Mr Stark :)

@gnomesukno 2 года назад

Very useful 👍 great job

@sqlbelle 2 года назад

Thank you Chris!

@sayakpalit3615 2 года назад

Very helpful tutorial.. 👍👍

@sqlbelle 2 года назад

Thank you Sayak!

@angelinajames9230 2 года назад

Very useful... thank you

@sqlbelle 2 года назад

Thank you Angelina

@farrukhkidwai 2 года назад

You are just awesome

@sqlbelle 2 года назад

Thank you Farrukh!

@BenCombes 2 года назад

Great video - just wondering how I would incorporate the leap year adjustment into the YTD, QTD, MTD example that you provided? Thanks

@BenCombes 2 года назад

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!

@KaunteyaShaw 2 года назад

Hello, can you plase share the link to the workbook? Thanks in advance, @sqlbelle !

@peterparker4358 2 года назад

Nice one really nice 👍 leap year issue I will not address as it has been corrected anyway🙂👍 thanks for posting 😃

@sqlbelle 2 года назад

Thank you Peter!

@RIVASCOSTA 2 года назад

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.

@anthonyburns7278 4 месяца назад

Thanks!

@sqlbelle 4 месяца назад

Thank you so much Anthony!

@YusrilSabillaAshar Год назад

thank uu for your amazing video, but after follow your instructions, why my choose duration can't move from YtD to QtD and MtD??

@dhruvzanzrkiya6801 Год назад

PLease create a video on how to calculate YoY,QoQ,MoM,WoW, pleaseeeeeeee

@sqlbelle Год назад

Will do! :)

@shivangsharma2329 Год назад

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).

@agger0083 Год назад

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 ?

@chao-chienchen4393 5 месяцев назад

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!

@leonguyen3264 6 месяцев назад

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?

@yomama20000 Год назад

Is there a workaround for MAKEDATE function for a "Live" database? MAKEDATE function isn't available for live connections.

@sandrahu7122 Год назад

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.

@sqlbelle Год назад

Hi Sandra! You can change the year to a blue pill (Discrete), and it will give you access to the discrete colors (not gradient)

@tonylu718 4 месяца назад

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?

@sqlbelle 4 месяца назад

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.

@sandrahu7122 Год назад

Would you be able to make the videos for the rest 3 LOD? 13-15

@sqlbelle Год назад

Thank you Sandra, I will!

@yanjihadi8529 2 года назад

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 :)

@sqlbelle 2 года назад

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

@LiHuang 2 года назад

Can't find the link to download the Workbook. Can you post it here. Thanks.

@sqlbelle 2 года назад

Thanks. TOP 15 LOD Expressions White Paper: www.tableau.com/about/blog/LOD-expressions

@agger0083 Год назад

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 !

@sqlbelle Год назад

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.

@agger0083 Год назад

@@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.

@sqlbelle Год назад

@@agger0083 That's great, thank you :)

@themantien Год назад

Why do you use the LOD max order date instead of just doing a normal max order date?

@sqlbelle Год назад

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.

@yashlokhande2859 2 года назад

@sqlbelle Can you please create a dashboard containing bins, sets, groups, parameters ?

@nimrodzaphnath5495 2 года назад

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.

@sqlbelle 2 года назад

Great point.

@ismailshaikh1741 Год назад

Very nice teaching mam..... But you didn't practiced LOD 13,14,15 😀..... Please give us the explanation 👍

@ballyb1 2 года назад

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QSIUdjfu8B0.html Appreciate any help on this as not sure how to fix it?