Need help modifying this approach and writing your own calculations? Book an office hour to work with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909 Ready to take your Tableau skills to the next level? Check out our upcoming classes here! www.eventbrite.com/o/onenumber-15678294163
Hi Eric.. Thanks for this videos it's really helpful for me. Can you make video on If I need to get the percent difference of the selected date range from the filter. For ex. July 15, 2023 to July 30, 2023. It shows the number of users within that specific date range i.e. 139,574. in the same card KPI, it should compare this number with previous period number and show percentage increase or decrease i.e. in this case July 1, 2023 to July 14, 2023. Thanks!
Thanks, I'm glad it was helpful! Does this video help? It covers most of the major topics you mentioned: onenumber.biz/blog-1/2021/9/5/month-over-month-comparison-summary-tiles-in-tableau
Thank you so much it's great video. I was wondering if you can please make a video on : where you have two filters where you choose different time periods from each filter and then the visual you have will compare these two periods. example compare April 2024 vs June 2023 or any other period/s . I was imagining where you choose April 2024 from one filter and June 2023 from other filter
That's a great video idea. We haven't made a video like that yet, but maybe the ideas in this video will help: ua-cam.com/video/KYr-qrcZYq4/v-deo.html&ab_channel=OneNumber-TableauExperts
Hi Eric love all your tutorials and I have many of them on repeat. Thanks for creating these, they are so easy to follow and helpful and your teaching style is perfect.
@@debmurphy4051 Hi Deb, we recommend checking out this video! onenumber.biz/blog-1/2022/10/26/fiscal-year-to-date-vs-prior-fiscal-year-to-date-in-tableau
@@onenumbertableau thanks so much. I had looked at that one before, but I neglected to change the data type from number to date. Works like a charm. Thanks! And hope I'm sending your kids to college with the number of times I watch your videos
Your videos are helping me a lot in day to day my tableau learning path. Can you please make a video on "how to calculate newly added ids to the newer date or end date which break up how many active vs inactive count between any two dates comparision dynamically ". Hope you will make a video on this.
This has been an ongoing issue that I am having. I have multiple clients and their FY/CY vary. Need assistance to make sure that the dates are pulling in the correct information i.e. If the date range is from 7.1.2022 - 4.23.2023, I need to be able to see the comparisons for all previous years for the same date range of 7.1 - 4.23. Can this be done?
Hi Lisa! I'd recommend checking this Fiscal Year video out. I think it has a lot of the info you need: onenumber.biz/blog-1/2022/10/26/fiscal-year-to-date-vs-prior-fiscal-year-to-date-in-tableau
Hi, thanks for sharing this. How would you do this for multiple dimension (i.e. sales, quantity, category, etc), and especially with metrics that require different aggregations (i.e. sum, avg)? I currently have parameters set up for users to select the dimension and parameters to select the metric.
Hey Kyle, I'd recommend checking out this video to learn more about how to incorporate this with different measures and aggregations: onenumber.biz/blog-1/2022/12/21/why-aggregations-are-important-in-tableau-calculations
@@onenumbertableau Thanks, that was helpful! Also any links/a possible future video on how to set up so that the user can select a dynamic date range and in the backend it can calculate the relative period? For example today is June 27th, but the user selects the date range of June 10th to June 16th 2023 and then the comparison period would calculate for June 10th to June 17th 2022 if it was set to YoY. Similarly, if it was set to WeekOverWeek, it would be compared to June 3rd to June 9th 2023.
@@user-rl9iz1sw2rYour best bet there is a the DATEADD function. For example, you might have a calc which returns values for "Current Period" and one that returns values for a "Prior Period". Here's how Prior Period would work IF [Date Field] >= DATEADD('year',-1,[Start Date Parameter]) AND [Date Field]
This is awesome!! Quick question regarding the trends - could you use that second measure (comparison sales) in the line chart that dynamically acts as the previous month/quarter/date or would I have to create a new metric/calculated field all together? Just so I could see visually how my current date range is doing compared to the previous one from a trended perspective.
Hey Shawn, sure! No reason you could do that. Let's use YTD as an example. If you did a Day of Year Calc...DATEPART('dayofyear',[Order Date]), put that on the Columns Shelf, then put the Year of Order Date on colors, that should work. You'd have to scale that out for each of the options (e.g. MTD, etc.) so it'd be a bit of work and you'd need a couple additional dynamic fields, but I don't see any reason that wouldn't work.
Hi Eric! Your videos are my go-to! Thank you for creating these videos! I just have 1 question... is it possible to do a comparison on a measure that is aggregated? For e.g. instead of Sales, I want to do it based on countd(ID). I tried to do it with IF selected period filter = TRUE then countd(ID) END, but I ended up getting that error that says that I can't mix aggregate and non-aggregate comparisons, I guess because countd(ID) will naturally be an aggregated value in the table. I've been stuck ever since :( Any guidance will be great!
Hi Cheryl! You can definitely do this for aggregated measures. However, you'll need to adjust the order of the calculation a bit. For Example, it would look something like this: COUNTD( IF [Date Range] = TRUE THEN [ID] END ) We have a full-length video about aggregation order of operations here: onenumber.biz/blog-1/2022/12/21/why-aggregations-are-important-in-tableau-calculations
@@onenumbertableau Hi again Eric, for (4), I was wondering if it's going to be same as (3) Last Qtr vs Last Qtr - around 8:55 min? Your example for (4) was Q1TD 2022 vs Q4TD 2021 and my % change didn't change... So I was thinking, if what I want is to compare QTD vs Last Yr's QTD (i.e. Q3TD 2023 vs Q3TD 2022) instead, how can I set it up? Do I need a double AND condition? Same question for (6) too, what if I need to compare October 2023 vs October 2022 instead?
@@cherypoot Hi Cheryl. I think it mostly comes down to changing the references. If you want to compare to the same quarter last year instead of last quarter you would swap the references from 1 to 4. For months, you would swap the references from 1 to 12. This video shows how to do this for quarters: onenumber.biz/blog-1/2023/1/4/how-to-tableau-quarter-to-date-vs-prior-year-quarter-to-date
Really nice video , any chance you can create MOM & YOY that are all in the PAST ( without TODAY). I work with datasets that are not current and need some help- THANKS
Sure! No reason you can't do that. You can hard code the date in a format like this #9-15-2022# as the end date or you can have the end date driven by a parameter (like this kb.tableau.com/articles/howto/creating-a-filter-for-start-and-end-dates-parameters) or even based on the latest date in your data set with a calculation like this {MAX([Date Field])} (More on that here: onenumber.biz/blog-1/2018/8/15/using-tableau-to-only-display-data-from-the-latest-date)
Hey Eric! your videos are really useful, really appreciate them! I was wondering if you could cover/know how to create a MoM waterfall in which you could see how some subcategory (for example "countries") contributed to this MoM variation.
Hey Juan, thanks for the comment and support! Here is a generic waterfall chart video we published in the fall. ua-cam.com/video/HVFXwVXx9F4/v-deo.html Do you have an example of the specific variation of waterfall chart you are looking for?
This video was super helpful! Is it possible to use this method with aggregated fields (i.e. Units Per Transaction or Average Sale Price)? The calculations seem to break when trying to use the date parameter filter...
It should be doable but you'll have to make sure the TRUE/FALSE check happens before the aggregation. So something like this is fine. AVG(IF [Date Logic] = TRUE THEN [Sales] END) But something like this will not work. IF [Date Logic] = TRUE THEN AVG([Sales]) END More on this concept and the aggregation error here: onenumber.biz/blog-1/2018/1/30/tableau-cannot-mix-aggregate-and-non-aggregate-comparisons-or-results-in-if-expressions
Hey so I'm not sure that I have a solution built which address exactly that but you can check out this walkthrough on popping a filter on and off a dashboard. If you download the associated workbook, you should be able to reverse engineer some of what you are looking for. onenumber.biz/blog-1/2022/3/30/pop-a-filter-on-and-off-a-tableau-dashboard
Need help setting up your own custom date field? Book an hour with Eric: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909 Check out the prequel to this video here! ua-cam.com/video/PM0O3uVJ-Bw/v-deo.html&ab_channel=OneNumber-TableauTutorials
Need help modifying this approach and writing your own calculations? Book an office hour to work with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909
Ready to take your Tableau skills to the next level? Check out our upcoming classes here! www.eventbrite.com/o/onenumber-15678294163
Hi Eric.. Thanks for this videos it's really helpful for me. Can you make video on If I need to get the percent difference of the selected date range from the filter. For ex. July 15, 2023 to July 30, 2023. It shows the number of users within that specific date range i.e. 139,574. in the same card KPI, it should compare this number with previous period number and show percentage increase or decrease i.e. in this case July 1, 2023 to July 14, 2023. Thanks!
Thanks, I'm glad it was helpful! Does this video help? It covers most of the major topics you mentioned: onenumber.biz/blog-1/2021/9/5/month-over-month-comparison-summary-tiles-in-tableau
Thank you so much it's great video. I was wondering if you can please make a video on : where you have two filters where you choose different time periods from each filter and then the visual you have will compare these two periods. example compare April 2024 vs June 2023 or any other period/s . I was imagining where you choose April 2024 from one filter and June 2023 from other filter
That's a great video idea. We haven't made a video like that yet, but maybe the ideas in this video will help: ua-cam.com/video/KYr-qrcZYq4/v-deo.html&ab_channel=OneNumber-TableauExperts
Hi Eric love all your tutorials and I have many of them on repeat. Thanks for creating these, they are so easy to follow and helpful and your teaching style is perfect.
Thank you so much Deb! We're so glad to hear you're finding this content useful!
@@onenumbertableau could you show how to adjust the Filters to October FY start. Thanks
@@debmurphy4051 Hi Deb, we recommend checking out this video! onenumber.biz/blog-1/2022/10/26/fiscal-year-to-date-vs-prior-fiscal-year-to-date-in-tableau
@@onenumbertableau thanks so much. I had looked at that one before, but I neglected to change the data type from number to date. Works like a charm. Thanks! And hope I'm sending your kids to college with the number of times I watch your videos
@@debmurphy4051 Haha thanks Deb! We don't have anything monetized right now but appreciate the sentiment! We're happy to know this is helping people!!
Amazing. Heavily underrated channel.
Thanks Salih! We're hoping to keep growing and helping more people!
Your videos are helping me a lot in day to day my tableau learning path. Can you please make a video on "how to calculate newly added ids to the newer date or end date which break up how many active vs inactive count between any two dates comparision dynamically ". Hope you will make a video on this.
Thanks Amit! Would this be similar to "How many new customers purchased from us today?" or something along those lines?
Thank you!
You're welcome!
Many thanks Eric. Your videos are always on point. You've added value to me.
Glad to hear it, thanks so much for the support!
Clear explanation. I enjoyed the video! Thanks a lot! :)
We are so glad to hear it. You are very welcome!
This has been an ongoing issue that I am having. I have multiple clients and their FY/CY vary.
Need assistance to make sure that the dates are pulling in the correct information i.e. If the date range is from 7.1.2022 - 4.23.2023, I need to be able to see the comparisons for all previous years for the same date range of 7.1 - 4.23. Can this be done?
Hi Lisa! I'd recommend checking this Fiscal Year video out. I think it has a lot of the info you need: onenumber.biz/blog-1/2022/10/26/fiscal-year-to-date-vs-prior-fiscal-year-to-date-in-tableau
thanks Eric, appreciate your work, this channel is really helpful
Thanks Michal! I'm glad you're enjoying it!!
Hi, thanks for sharing this. How would you do this for multiple dimension (i.e. sales, quantity, category, etc), and especially with metrics that require different aggregations (i.e. sum, avg)? I currently have parameters set up for users to select the dimension and parameters to select the metric.
Hey Kyle, I'd recommend checking out this video to learn more about how to incorporate this with different measures and aggregations: onenumber.biz/blog-1/2022/12/21/why-aggregations-are-important-in-tableau-calculations
@@onenumbertableau Thanks, that was helpful! Also any links/a possible future video on how to set up so that the user can select a dynamic date range and in the backend it can calculate the relative period? For example today is June 27th, but the user selects the date range of June 10th to June 16th 2023 and then the comparison period would calculate for June 10th to June 17th 2022 if it was set to YoY. Similarly, if it was set to WeekOverWeek, it would be compared to June 3rd to June 9th 2023.
@@user-rl9iz1sw2rYour best bet there is a the DATEADD function. For example, you might have a calc which returns values for "Current Period" and one that returns values for a "Prior Period".
Here's how Prior Period would work
IF [Date Field] >= DATEADD('year',-1,[Start Date Parameter]) AND [Date Field]
Hi, what if I have multiple KPIs
You'll want to create individual calculations for each of those metrics.
This is awesome!! Quick question regarding the trends - could you use that second measure (comparison sales) in the line chart that dynamically acts as the previous month/quarter/date or would I have to create a new metric/calculated field all together? Just so I could see visually how my current date range is doing compared to the previous one from a trended perspective.
Hey Shawn, sure! No reason you could do that. Let's use YTD as an example. If you did a Day of Year Calc...DATEPART('dayofyear',[Order Date]), put that on the Columns Shelf, then put the Year of Order Date on colors, that should work. You'd have to scale that out for each of the options (e.g. MTD, etc.) so it'd be a bit of work and you'd need a couple additional dynamic fields, but I don't see any reason that wouldn't work.
Hi Eric! Your videos are my go-to! Thank you for creating these videos!
I just have 1 question... is it possible to do a comparison on a measure that is aggregated? For e.g. instead of Sales, I want to do it based on countd(ID).
I tried to do it with IF selected period filter = TRUE then countd(ID) END, but I ended up getting that error that says that I can't mix aggregate and non-aggregate comparisons, I guess because countd(ID) will naturally be an aggregated value in the table. I've been stuck ever since :( Any guidance will be great!
Hi Cheryl! You can definitely do this for aggregated measures. However, you'll need to adjust the order of the calculation a bit.
For Example, it would look something like this:
COUNTD(
IF [Date Range] = TRUE THEN [ID] END
)
We have a full-length video about aggregation order of operations here: onenumber.biz/blog-1/2022/12/21/why-aggregations-are-important-in-tableau-calculations
@@onenumbertableau Lifesaver! I was fiddling with it for a while... didn't know the solution was so simple. Thanks for the link! I'll take a look! :)
@@cherypoot You're welcome!
@@onenumbertableau Hi again Eric, for (4), I was wondering if it's going to be same as (3) Last Qtr vs Last Qtr - around 8:55 min? Your example for (4) was Q1TD 2022 vs Q4TD 2021 and my % change didn't change...
So I was thinking, if what I want is to compare QTD vs Last Yr's QTD (i.e. Q3TD 2023 vs Q3TD 2022) instead, how can I set it up? Do I need a double AND condition?
Same question for (6) too, what if I need to compare October 2023 vs October 2022 instead?
@@cherypoot Hi Cheryl. I think it mostly comes down to changing the references. If you want to compare to the same quarter last year instead of last quarter you would swap the references from 1 to 4. For months, you would swap the references from 1 to 12.
This video shows how to do this for quarters: onenumber.biz/blog-1/2023/1/4/how-to-tableau-quarter-to-date-vs-prior-year-quarter-to-date
Really nice video , any chance you can create MOM & YOY that are all in the PAST ( without TODAY). I work with datasets that are not current and need some help- THANKS
Sure! No reason you can't do that. You can hard code the date in a format like this #9-15-2022# as the end date or you can have the end date driven by a parameter (like this kb.tableau.com/articles/howto/creating-a-filter-for-start-and-end-dates-parameters) or even based on the latest date in your data set with a calculation like this {MAX([Date Field])} (More on that here: onenumber.biz/blog-1/2018/8/15/using-tableau-to-only-display-data-from-the-latest-date)
Hey Eric! your videos are really useful, really appreciate them! I was wondering if you could cover/know how to create a MoM waterfall in which you could see how some subcategory (for example "countries") contributed to this MoM variation.
Hey Juan, thanks for the comment and support! Here is a generic waterfall chart video we published in the fall. ua-cam.com/video/HVFXwVXx9F4/v-deo.html
Do you have an example of the specific variation of waterfall chart you are looking for?
This video was super helpful! Is it possible to use this method with aggregated fields (i.e. Units Per Transaction or Average Sale Price)? The calculations seem to break when trying to use the date parameter filter...
It should be doable but you'll have to make sure the TRUE/FALSE check happens before the aggregation.
So something like this is fine.
AVG(IF [Date Logic] = TRUE THEN [Sales] END)
But something like this will not work.
IF [Date Logic] = TRUE THEN AVG([Sales]) END
More on this concept and the aggregation error here: onenumber.biz/blog-1/2018/1/30/tableau-cannot-mix-aggregate-and-non-aggregate-comparisons-or-results-in-if-expressions
What if you wanted to select the period date to end last November and 12 months prior to that to compare previous same period. Then how do you do it?
Hey so I'm not sure that I have a solution built which address exactly that but you can check out this walkthrough on popping a filter on and off a dashboard. If you download the associated workbook, you should be able to reverse engineer some of what you are looking for. onenumber.biz/blog-1/2022/3/30/pop-a-filter-on-and-off-a-tableau-dashboard
Need help setting up your own custom date field? Book an hour with Eric: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909
Check out the prequel to this video here! ua-cam.com/video/PM0O3uVJ-Bw/v-deo.html&ab_channel=OneNumber-TableauTutorials