Need help modifying this approach and writing your own calculations? Book an office hour to work with Eric here: onenumber.biz/office-hours | Ready to take your Tableau skills to the next level? Check out our upcoming classes here! onenumber.biz/onenumber-tableau-classes
Hello, Eric. Thank you so much for saving my day with this wonderful series of posts about date period filters; could you please tell me what the calculations for these are? (Last 7 days,Last 30 days,Previous 30 days,Previous 3 months) and Also, what is the distinction between previous and last?
Hi Eric, I am following since I found your videos, I do have a question is how to get same case options for "Today", "Yesterday", "Last Week", etc. The date formula always get me confuse. I really appreciate you could do a video about it. Thank you!
Hey Luis. No problem. DATEDIFF can help you with all of these. Today DATEDIFF('day',[Date Field],TODAY()) = 0 Yesterday DATEDIFF('day',[Date Field],TODAY()) = 1 Last Week DATEDIFF('week',[Date Field],TODAY()) = 1
Hello, thank you very much for the tutorial, but how do I have a month filter within this scenario? example I choose the month and the filter calculates from the choice.
Hi George. are you saying you want your user to be able to individually select any month they want? And is that in addition to other options like Year to Date or do you just want users to have a month filter option? You might find this video interesting: onenumber.biz/blog-1/2022/3/30/pop-a-filter-on-and-off-a-tableau-dashboard
Hey Eric great video and explanation! I have a question, I am trying to create a dashboard where the user can change the date view from weekly/monthly/quarterly, so the date field changes from DAY(Date) to MONTH(Date) etc, is it possible using this method? Thank you!
Hi Roey! Sure! You can either do this by swapping worksheets or using a Parameter tied to the DATETRUNC function. Here's a walkthrough on the DATETRUNC approach: onenumber.biz/blog-1/2018/2/7/creating-a-dynamic-date-field-in-tableau
Follow up question, any guides on how to conditionally format the dates based on the parameter ? For example year to show 2023, quarter to show Q1/Q2, month shoes month name etc
@@roeystern6754 That part is tricky. Sometimes I'll create another sheet which will basically just replace the axis that swaps between years, Month Names, etc. You'll probably need a custom date label calc tied to that same parameter to do the trick.
Hey Zack, definitely. If you don't need the preset options, you can just put a date field on filters and allow the users to change the range of values. If you want to accomplish both (preset options and range), check this out: th-cam.com/video/OBOf6zbYHek/w-d-xo.html&ab_channel=OneNumber-TableauExperts
Hey Sandra! I'm not sure how to advise you without seeing the workbook. Can you paste the calculations you used here and maybe I can work something out?
@@onenumbertableau Hi first I used this FY CLOSE Date (for calcs) DATEADD('month',3,[Closure Date]) And then this is the calculation i used the parameters. Unfortunately I cant post the workbook because it has PII. Case [Closure Date Filter] When 1 THEN YEAR([FY Close Date (for calcs)])=YEAR(TODAY()) When 2 THEN (YEAR([FY Close Date (for calcs)])= YEAR(TODAY())-1) AND DATEPART('dayofyear',[FY Close Date (for calcs)])
@@villitakaraoke I think the issue might be the TODAY() function. For instance, the actual year of today (nov. 9th, 2022) is 2022 but the FY of today is 2023. Try to create a Calc called "FY of Today" with this logic: DATEADD('month',3,TODAY()) and then reference that calculation instead of TODAY() in your calculation.
How would you create a custom filter if you'd want it to populate the first occurances of data? EX: sales data and you want to have filter options for first month, 6 months, etc. so not recent data but historicals
Interesting. I suppose you need to change your anchor date from TODAY() to {MIN([Date Field])}. First Month: DATEDIFF('month',{MIN([Date Field])},[Date Field]) = 0 First 6 months: DATEDIFF('month',{MIN([Date Field])},[Date Field])
Instead of referencing TODAY in your calculations, can you reference the latest date in your data set instead? This should give you an idea for the general concept: onenumber.biz/blog-1/2020/3/11/how-to-make-dynamic-parameters-in-tableau-new-feature
Thank you for the great video. Could you help me with the formula for the Next 1 week, Next 1 Month, Next 6 Months and Next 1 year. My data has future dates
Hey Gilton! I think this is what you're looking for. Next 1 Week: DATEDIFF('week',[Date Field],TODAY()) = -1 Next 1 Month: DATEDIFF('month',[Date Field],TODAY()) = -1 Next 6 Months: DATEDIFF('month',[Date Field],TODAY()) < 0 AND DATEDIFF('month',[Date Field],TODAY()) >= -6 Next 1 Year: DATEDIFF('year',[Date Field],TODAY()) = -1
Check out the sequel to this video, "How to Tableau" Selected vs. Comparison Date Period Filter" out now! th-cam.com/video/l2EYtIXhm9M/w-d-xo.html&ab_channel=OneNumber-TableauTutorials | Need help customizing your own filter? Book an office hour with Eric: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909
Need help modifying this approach and writing your own calculations? Book an office hour to work with Eric here: onenumber.biz/office-hours | Ready to take your Tableau skills to the next level? Check out our upcoming classes here! onenumber.biz/onenumber-tableau-classes
Thank you! This is so helpful as I learn Tableau on the fly for work requests at my job.
Hi Jenna, we are so glad this was helpful. Thanks for letting us know!
Great video! This was easy to follow along and I was able to get this to work for my own project in less than 10 minutes.
Amazing! Thank you for letting us know Dan!
This was super helpful, thanks! Especially appreciated when you showed how you'd trouble shoot errors--thanks for walking through that!
I'm glad you benefitted from my screw up!! :)
Nice video again. I will really love the YTD vs LY Video. Thank you for what you are doing.
Absolutely! I'm so glad this resonated!
YES! Would love to see the year comparison tutorial pls
Awesome! Thanks for the feedback. I'm top add it to my log of videos to shoot!
Thank you again for the encouragement. The sequel is out now here! th-cam.com/video/l2EYtIXhm9M/w-d-xo.html&ab_channel=OneNumber-TableauTutorials
Very helpful, thank you!
We're glad to hear it, thanks for letting us know!
Thanks Eric! How would I be able to view the % difference between each date in this particular date parameter?
You're welcome! I recommend checking this video out: th-cam.com/video/l2EYtIXhm9M/w-d-xo.html&ab_channel=OneNumber-TableauExperts
You are the best!!
Muchas gracias Eric!
De nada Joaquin!
Hello, Eric. Thank you so much for saving my day with this wonderful series of posts about date period filters; could you please tell me what the calculations for these are? (Last 7 days,Last 30 days,Previous 30 days,Previous 3 months) and Also, what is the distinction between previous and last?
Hi Manoj. Thank you for the kind words! Here's how I would tackle your prompts.
Last 7 Days:
DATEDIFF('day',[Date],TODAY())
Hi Eric, I am following since I found your videos, I do have a question is how to get same case options for "Today", "Yesterday", "Last Week", etc. The date formula always get me confuse. I really appreciate you could do a video about it. Thank you!
Hey Luis. No problem. DATEDIFF can help you with all of these.
Today
DATEDIFF('day',[Date Field],TODAY()) = 0
Yesterday
DATEDIFF('day',[Date Field],TODAY()) = 1
Last Week
DATEDIFF('week',[Date Field],TODAY()) = 1
very helpful, thanks! Love your channel
Thanks so much Michal!!
Hello, thank you very much for the tutorial, but how do I have a month filter within this scenario? example I choose the month and the filter calculates from the choice.
Hi George. are you saying you want your user to be able to individually select any month they want? And is that in addition to other options like Year to Date or do you just want users to have a month filter option? You might find this video interesting: onenumber.biz/blog-1/2022/3/30/pop-a-filter-on-and-off-a-tableau-dashboard
@@onenumbertableau THANK YOU VERY MUCH!!!
Hey Eric great video and explanation! I have a question, I am trying to create a dashboard where the user can change the date view from weekly/monthly/quarterly, so the date field changes from DAY(Date) to MONTH(Date) etc, is it possible using this method? Thank you!
Hi Roey! Sure! You can either do this by swapping worksheets or using a Parameter tied to the DATETRUNC function. Here's a walkthrough on the DATETRUNC approach: onenumber.biz/blog-1/2018/2/7/creating-a-dynamic-date-field-in-tableau
Thank you for the quick response!
Follow up question, any guides on how to conditionally format the dates based on the parameter ? For example year to show 2023, quarter to show Q1/Q2, month shoes month name etc
@@roeystern6754 That part is tricky. Sometimes I'll create another sheet which will basically just replace the axis that swaps between years, Month Names, etc. You'll probably need a custom date label calc tied to that same parameter to do the trick.
THANKS!!!!!!!!
You are most welcome!
Is there allow a user the ability to do a custom input range to filter for, versus have to select from a preset period?
Hey Zack, definitely. If you don't need the preset options, you can just put a date field on filters and allow the users to change the range of values. If you want to accomplish both (preset options and range), check this out: th-cam.com/video/OBOf6zbYHek/w-d-xo.html&ab_channel=OneNumber-TableauExperts
Awesome, I tried your video doing the same using what I learned from your video of FYTD and Previous FYTD but its all messed up. Any suggestions?
Hey Sandra! I'm not sure how to advise you without seeing the workbook. Can you paste the calculations you used here and maybe I can work something out?
@@onenumbertableau
Hi first I used this
FY CLOSE Date (for calcs)
DATEADD('month',3,[Closure Date])
And then this is the calculation i used the parameters. Unfortunately I cant post the workbook because it has PII.
Case [Closure Date Filter]
When 1 THEN YEAR([FY Close Date (for calcs)])=YEAR(TODAY())
When 2 THEN (YEAR([FY Close Date (for calcs)])= YEAR(TODAY())-1) AND DATEPART('dayofyear',[FY Close Date (for calcs)])
@@villitakaraoke I think the issue might be the TODAY() function. For instance, the actual year of today (nov. 9th, 2022) is 2022 but the FY of today is 2023. Try to create a Calc called "FY of Today" with this logic: DATEADD('month',3,TODAY()) and then reference that calculation instead of TODAY() in your calculation.
@@onenumbertableau THANK YOU SO MUCH!!! it worked like a charm. This is becoming my favorite YT channel.
@@villitakaraoke Thank you Sandra! I'm so glad to hear it worked.
Hello. Can you please tell me how did you calculate Sales % Difference field?
Do you mean the follow up video? You can find that here! th-cam.com/video/l2EYtIXhm9M/w-d-xo.html
How would you create a custom filter if you'd want it to populate the first occurances of data? EX: sales data and you want to have filter options for first month, 6 months, etc. so not recent data but historicals
Interesting. I suppose you need to change your anchor date from TODAY() to {MIN([Date Field])}.
First Month: DATEDIFF('month',{MIN([Date Field])},[Date Field]) = 0
First 6 months: DATEDIFF('month',{MIN([Date Field])},[Date Field])
Hi, eric how do i correct this if my data only lasts until 2017?
Instead of referencing TODAY in your calculations, can you reference the latest date in your data set instead?
This should give you an idea for the general concept: onenumber.biz/blog-1/2020/3/11/how-to-make-dynamic-parameters-in-tableau-new-feature
Thank you for the great video. Could you help me with the formula for the Next 1 week, Next 1 Month, Next 6 Months and Next 1 year. My data has future dates
Hey Gilton! I think this is what you're looking for.
Next 1 Week:
DATEDIFF('week',[Date Field],TODAY()) = -1
Next 1 Month:
DATEDIFF('month',[Date Field],TODAY()) = -1
Next 6 Months:
DATEDIFF('month',[Date Field],TODAY()) < 0 AND DATEDIFF('month',[Date Field],TODAY()) >= -6
Next 1 Year:
DATEDIFF('year',[Date Field],TODAY()) = -1
@@onenumbertableau wow thatnk you Eric. I will try it
How to add % change in sales by category that will update as we toggle the parameter ?
Hi! Are you thinking of something like this? onenumber.biz/blog-1/2021/9/5/month-over-month-comparison-summary-tiles-in-tableau
This may also be helpful: th-cam.com/video/l2EYtIXhm9M/w-d-xo.html&ab_channel=OneNumber-TableauExperts
@@onenumbertableau Thank you this was very helpful
@@sania1108 You're welcome!
Check out the sequel to this video, "How to Tableau" Selected vs. Comparison Date Period Filter" out now! th-cam.com/video/l2EYtIXhm9M/w-d-xo.html&ab_channel=OneNumber-TableauTutorials | Need help customizing your own filter? Book an office hour with Eric: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909