You could use the LOWER() function to keep the parameter options in title case. Can you advise if the makedate function will work when published to tableau server?
What about if I had DateCreated consisting of 5 years worth of data. You created a calculated field of MakeDate. How would I use DateCreated to show me the previous year as my Equaliser
Hi Anthony. Thanks so much for your sharing. I have followed all your steps to do the comparative figure for four years sales. However, the line chart cannot be synchronies axis and all lines have been separate to show in different column. Would you please advise how could I fix it? Thank you.
Hi It is really useful . I have one question if I want to compare with last year same week day how to do it ? For example this year Jun 11 is Friday vs. 2020 Jun 12 (Friday)
This only works for extract and does not work with tableau server live , what is the alternative for this function? (the makedate function does not work when it is live)
You must be using a live connection which doesn't accommodate MAKEDATE. Try the solution offered here: or switch to an extract community.tableau.com/s/question/0D54T00000C6mzeSAB/makedate-function-workaround
That's a good question, try it and let us know! I will say that the TRUNC function respects the fiscal year property. help.tableau.com/current/pro/desktop/en-us/dates_fiscal.htm
@@AnthonySmoak Thank you for the help and the link. After a bit of trying I finally realized as long as you take out the day granularity option the chart will render as desired. Then I just had to leave the relative date filter off since that will not take into consideration the change in fiscal year start. Thank you!
@@andreajohnston9390 Hey! Can you elaborate a little more on your solution? I too would like to get the axis to display starting with our federal fiscal year (Oct) instead of January and I'm not following the changes you made.
Depending upon your use case, you can use a max LOD function in combination with datepart() to find the greatest year in your dataset instead of hard coding. Hardcoding dates was for ease of understanding the concept.
Thanks, very helpful. However, the tooltips of Equalize date is showing 2018 for year 2017, would be perfect if we could fix this. New to Tableau from Power BI.
Thank you, Anthony. I'm new to Tableau. I believe this strategy may not work if there is a leap lear involved. What I think happens is the additional day (2/29) gets added to March. Would you agree? Food for thought.
I am confused about the MakeDate function. When the year is specified as 2018, why are the dates that were originally in 2017 still 2017? The function obviously worked, but I still don't understand the logic of it. Since you've forced a 2018 date, logic dictates to me that all the values should be for for 2018. Instead, it's created a separate line for what's actually 2018. Can someone please explain that for me? Thank you, in advance, for any clarification, and thank you for making this post.
You are correct that we have shifted the 2017 dates to 2018. Since the original (unshifted) order date years are colored by year, this forces the separation into two lines. Hope that helps!
Hello when I create the Calculated field DATETRUNC('day',[Date Granulariy Param],[Equalizer Date]) it gives me an error 'DateTrunc is being called with a (string,string,date) did you mean (string,datetime,string). Why am I getting this error and how do I fix it. Thanks.
Hi Anthony I am new in tableau area. I have created the same year over year sales trend using sample superstore data set; but what is happening when i am selecting the months from date granual param it is giving me an error like ' argument months to datetrunc must specify a datepart('year', 'month',etc.)' Please guide to resolve this issue
If you created the parameter with the same spelling as in the video, you shouldn't have any issue passing the datepart to datetrunc(). Double check to make sure the spelling of your parameter options are correct.
Anthony, how to fix a fiscal year with a particular date. Exa- Fiscal year starts from 26th July 2020 to 31st July 2021.We have to fix the quarters as follows & find out the sales for each quarter. Q1 26th Jul 2020 24th Oct 2020 90 Days Q2 25th Oct 2020 23rd Jan 2021 90 days Q3 24th Jan 2021 1st May 2021 97 days Q4 2nd May 2021 31st Jul 2021 90 days Please advice.
Hey Anthony.. Please make a video to show Latest year Sales and Sales growth % over last year with a Year,month filters so that users can see the sales and growth only for the selected year,month.. Please show the best approach LOD or SETS ..
This is awesome Anthony! liked it
Extremely useful, it took me a few days before seeing this video to figure things out. Appreciate it!
This is so incredibly helpful!! You just saved me a lot of time. Thank you!!
Thanks for the kind words!
Extremely helpful.. You always come out with good and useful content.. Thank you.. Looking forward for your LIVE sessions of makeovermoday..
Very Helpful....I got a lot of information from this video. Thank You
Thanks for leaving a comment!
Loved this one, very practical!
Very useful! thanks for sharing your knowledge.
Thank you! It's very helpful
Glad to hear that!
Excellent! THANKS!!! I wish to know how to do this for more than two years, let's say 2017, 2018 and 2019. Is that possible?
Anthony this is really helpful
You could use the LOWER() function to keep the parameter options in title case.
Can you advise if the makedate function will work when published to tableau server?
What about if I had DateCreated consisting of 5 years worth of data. You created a calculated field of MakeDate. How would I use DateCreated to show me the previous year as my Equaliser
Hi Anthony. Thanks so much for your sharing. I have followed all your steps to do the comparative figure for four years sales. However, the line chart cannot be synchronies axis and all lines have been separate to show in different column. Would you please advise how could I fix it? Thank you.
Extremely helpful!
Thanks Anthony for sharing
Hi It is really useful . I have one question if I want to compare with last year same week day how to do it ? For example this year Jun 11 is Friday vs. 2020 Jun 12 (Friday)
This only works for extract and does not work with tableau server live , what is the alternative for this function? (the makedate function does not work when it is live)
Hi, thank you, I dont have the option "MAKEDATE", do you know if is it normal? If yes, is there any other formula?
You must be using a live connection which doesn't accommodate MAKEDATE. Try the solution offered here: or switch to an extract community.tableau.com/s/question/0D54T00000C6mzeSAB/makedate-function-workaround
Great work.. thnks for the video
I appreciate the comment; thank you!
Fantastic!! Thank you
Very helpful video. Will this approach still work if you change the fiscal year start property on the date field?
That's a good question, try it and let us know! I will say that the TRUNC function respects the fiscal year property. help.tableau.com/current/pro/desktop/en-us/dates_fiscal.htm
@@AnthonySmoak Thank you for the help and the link. After a bit of trying I finally realized as long as you take out the day granularity option the chart will render as desired. Then I just had to leave the relative date filter off since that will not take into consideration the change in fiscal year start. Thank you!
@@andreajohnston9390 Hey! Can you elaborate a little more on your solution? I too would like to get the axis to display starting with our federal fiscal year (Oct) instead of January and I'm not following the changes you made.
So what happened when it got to 2019? Did you have to go in and change your calculated fields that had a hard code of 2018 in there?
Depending upon your use case, you can use a max LOD function in combination with datepart() to find the greatest year in your dataset instead of hard coding. Hardcoding dates was for ease of understanding the concept.
Thanks, very helpful. However, the tooltips of Equalize date is showing 2018 for year 2017, would be perfect if we could fix this.
New to Tableau from Power BI.
Thank you, Anthony. I'm new to Tableau. I believe this strategy may not work if there is a leap lear involved. What I think happens is the additional day (2/29) gets added to March. Would you agree? Food for thought.
Good point Chris. I believe a leap year value would return NULL if not compared with other leap years. Thanks for watching.
I am confused about the MakeDate function. When the year is specified as 2018, why are the dates that were originally in 2017 still 2017? The function obviously worked, but I still don't understand the logic of it. Since you've forced a 2018 date, logic dictates to me that all the values should be for for 2018. Instead, it's created a separate line for what's actually 2018. Can someone please explain that for me? Thank you, in advance, for any clarification, and thank you for making this post.
You are correct that we have shifted the 2017 dates to 2018. Since the original (unshifted) order date years are colored by year, this forces the separation into two lines. Hope that helps!
Hello when I create the Calculated field DATETRUNC('day',[Date Granulariy Param],[Equalizer Date]) it gives me an error 'DateTrunc is being called with a (string,string,date) did you mean (string,datetime,string). Why am I getting this error and how do I fix it. Thanks.
Odell Ping You need to use DATETRUNC(‘[Date Gran Param]’,[Equalizer Date]) ;)
What if I have 5 or 6 years and want to do the comparison??
Please advise
Have you experimented with some data and run into issues?
can you please provide link for the data.
Hi Anthony I am new in tableau area. I have created the same year over year sales trend using sample superstore data set; but what is happening when i am selecting the months from date granual param it is giving me an error like
' argument months to datetrunc must specify a datepart('year', 'month',etc.)'
Please guide to resolve this issue
If you created the parameter with the same spelling as in the video, you shouldn't have any issue passing the datepart to datetrunc(). Double check to make sure the spelling of your parameter options are correct.
Thanks Anthony you were right i have misspelt the word Month to months that is why it is giving error
@@shubhankartiwari876 Great, glad we solved the problem.
Yeah absolutely you are fantastic guru of tableau
Anthony, how to fix a fiscal year with a particular date. Exa- Fiscal year starts from 26th July 2020 to 31st July 2021.We have to fix the quarters as follows & find out the sales for each quarter.
Q1 26th Jul 2020 24th Oct 2020 90 Days
Q2 25th Oct 2020 23rd Jan 2021 90 days
Q3 24th Jan 2021 1st May 2021 97 days
Q4 2nd May 2021 31st Jul 2021 90 days
Please advice.
Have you looked at the following? help.tableau.com/current/pro/desktop/en-us/dates_fiscal.htm
Hey Anthony.. Please make a video to show Latest year Sales and Sales growth % over last year with a Year,month filters so that users can see the sales and growth only for the selected year,month..
Please show the best approach LOD or SETS ..
where's I can find the dataset? Please attach the dataset