Really good tip, Andy. I use DATEADD so rarely, but it's really powerful. Having this in the back of my mind helped resolve a problem today. Used DATEADD to create a hidden reference line (max date plus 2 weeks) to make space at the end of an axis. Allowed me to tidy up labels on a series of line charts so they were beside the last mark instead of risking overlapping the line.
I honestly don't think I could give this a bigger thumbs up! One thing to note, I needed this to be a dynamic report that pivots on the current month so I added a filter with a calculation as below if DATENAME('month',[Date])= DATENAME('month',TODAY()) then 'Y' else 'N' end and left it on Y. Boom dynamic month based YOY reporting!
Thanks for the video Andy. Like Andrzej, I have also been trying to figure out how to do something like this taking fiscal year into account for quite some time. How would I adjust the formulas?
Hi Andy, thanks for the video. Just an observation, I'm not sure why nobody from top comments has noticed, The YoY you got is -ve when clearly sales this year has been greater than PY 😊 Regards, Mayank
@ Andy Kriebel, In your example, you have used only 1-year data. But I have 5 years of data, and YTD and PYTD is getting added for 5 last years using the same formula. Can you please let me know how to check only YTD for 2019 and PYTD for 2018.
Hi Andy! Thanks a LOT!!! I'm new to Tableau so the question may seem simple but anyway...is there any way to compare the period of the current year with the same period of the last year so that the dates are not fixed. For example, a period of 12 months.
Hi Andy, great tip. Love your videos. I have a question, when i do YTD vs PTD calculations i get the right data. My dashboard is set to show this year data, when they select last years data, the Prior year is blank. How do i fix that it picks up last year data anytime a previous year or the last two years are selected to display those KPIs? Please and thank you for everything
Hey Andy. This is awesome. Thnx :) I have a small requirement: between selected date ranges(start date and end date), I need to see the no of records. but I have 3 years of data. so I tried to use start date, end date as date_parameter displayed as a calendar. But the problem is. when I publish this, the end date is not updating to today(dynamically).. can u help me there?
Hi Andy! Thanks a lot for the video! Can you please help me on the below... From the above video, instead of choosing the max date i.e. the date is not fixed and I want to calculate PY and CY sales from the range of dates that I have chosen from the date filter... The date range should be dynamic... Could you please provide solution for this!! Thanks in advance!!
Hi Andy, Great concept! Tried the same with dynamic frequency and dynamic dimension. When I tried fixed calculation inside if or case statement, I am getting a different sum. But when I use it separately , I get the right result. Im just confused . fixed on different dimension inside case/if work differently? im stuck .. Pls help me .. Thank u in advance. This is the calc I have used.. if [Choose the Dimension] = 'Region' then { FIXED [Region] : SUM(if ([Order Date]>=[Current Month/Week/Quarter/Year Start Date ] and [Order Date] =[Current Month/Week/Quarter/Year Start Date ] and [Order Date] =[Current Month/Week/Quarter/Year Start Date ] and [Order Date] =[Current Month/Week/Quarter/Year Start Date ] and [Order Date]
Great video, thanks. Is there a way to dynamically control all these four measures. For example; if a user would like to have a drop down month filter and based on the month selection the four fields gets calculated. Thanks in advance.
Great video. Thank you This always shows the most recent month and year in the view. How would I show different months and years and the same month in the prior year? For example, if the user wants to see June 2022, compare it to June 2021. Would be great to have a month and year filter selection.
@@vizwiz Thanks for the reply. Is this something you can create another video and show us? I am sure everyone would want to have a chose of a month and year selection.
Hi Andy! Thank you for the video! I forgot to mention that the fiscal year at my company starts in July and not January. Since DATETRUNC ignores fiscal years, how would the formulas look different? I apologize, I should have mentioned this in my original post.
Andrzej Szczurko you would have to create another field that has the fiscal start, then ensure the dates are between that date and the other that I showed.
Thanks a lot Andy! One question, can this sheet that you created be filtered by your order date? I think that it is a bit tricky because if you select only a specific order date then you will not have PY Sales. Any suggestions on this one?
It's definitely possible. Off the top of my head, I think a parameter should be used instead of a filter. Then you create your calcs based on that date.
@@vizwiz but only with parameter and calculated fields? Can you think of any other possible way? I can't thank you enough for your videos and your quick response!
Thanks, Andy. Great way to do this! I had a little different approach, but this one works great. I wonder if it's possible to do conditional formatting on the pos/neg ratio (ONLY!) ? I desperately tried to do so and to let automatically color the negative ratios to red and the positive ones to green...I ended up with having just a quick and dirty solution...I calculated the ratios in another sheet which included conitional formatting...and put these 'two tables' in a dashboard at the end. Is there a more handy way to do it?
@@vizwizThanks, but I've seen this before. I had a slightly different demand. At the end, I resolved this by putting three dummy-0 measures for each column in the column shelf so I could format each column separately. The only problem I had was that I couldn't use the measure values as column names, since I had to show this table for orders AND sales and so two have e.g. Ø 2019-title twice. I did the column labeling directly in the text options eventuelly, and it worked well. Thank you for these vids, though. They help a lot to get the idea of tableau, especially when one's used to excel logic.
Thanks, One question - above example is based on reference to current/latest period. If the requirement is to link dynamically to any month period and show comparison as of that month is it possible using this methodology. requirement is to link calculation to filter selection !
@@vizwiz That’s right Andy but my emphasis was actually on subtracting PY from CY (CY-PY) and not the other way round as you did in the video because PY-CY make the interpretation of the increase and decrease misleading.
Hi Andy, thanks for your video, helped a lot! I was also trying to make the same calculation but not for sales, but for a COUNTD - I’m calculating the number of distinct values in YTD. Unfortunately, it ended up with a mistake of mixing aggregate and non-aggregate arguments. Can you maybe give a tip on that how can such YTD calculations be made? Many thanks! My input for YTD CY was: if [CalendarDay]>=[Jan 1st CY] and [CalendarDay]
It looks like your COUNTD is in the wrong spot. Where you have it now, the countd is calculated inside of the IF statement, thus mixing aggregate and non-aggregate. Try this - COUNTD( if [CalendarDay] >= [Jan 1st CY] and [CalendarDay]
andy, is it possible to add background color change depend on the percentage value,,like red if minus or green if plus? I've tried create that but kinda stuck here
Hi, When I apply these calculations, I am only getting back figures for 1 month (Jan CY) and the same 1 month (PY). I am essentially trying to visualise a dual axis line/area chart that shows PY versus CY. I want to implement the calcs, so they dynamically pull through most current year and prior year as more data is added. Thanks
Hi Andy, thank you for the reply. If I could ask one more question? I have been tasked with creating a dual axis chart that tracks percentage of total for PY versus CY. I have been able to create LOD calcs that always find the most recent month for the two figures needed to perform the division. However, I’m struggling to find a way to create dynamic calcs that create percentage of total for every month and continue to keep the data always within the most recent 24 month period, especially because two columns are involved to produce each percentage of total across the years. Hopefully this makes sense? Any help would be greatly appreciated. Regards
In date filter, if am changing year suppose we have 3 years data 2017,18,19. and want comparison between 17 and 18. it does not work properly, because in most recent adate we are picking max date and it is comparing with that. Can you suggest anything?
Hi there is there a way to calculate YTD sales depending on the date I have selected in a filter and compare it the the prior YTD sales reflecting the same period? For example 31st July 2019 vs. 31st July 2018…. Many thanks
Can you help with how this can be done for more than 1 year ... so for example o have data from 2019-21 and want that when the filter says 2020 the previous year should be 2019 or if I say 2021 then it should say 2020
How to create YTD as well as quater values in the cross tab? Beacuse if we add date to column it will have drill down option . It we keep year it will show year values ..Once we drill down then year will be gone quater wise data only be there...I need both in same view. 2018 YTD q1 q2 q3 q4
How would you approach this if, instead of Sales, you had a calculated field (aggregated) that you wanted to return the current and previous year for? You can't simply replace Sales with this calculated field because you would be mixing aggregate and non-aggregate things in the IF statement
Very useful, thank you. I applied the count for SUM aggregates, however I stumble at the YOY and YOY % calculation, where I need to count distinctly the number of order. Hint please!
@@vizwiz life saving, thank you!! I'd like to ask for one piece of advice, in the sources there are dates even from april 2021, so for the "Most recent date", I get more than I need. What could I do here? Thanks a million!
Hi Andy i am huge fan !!! . I have question how would you do this for a fiscal year that starts on the 8th of April i have literally blown my top off trying to figure out a solution
@@vizwiz hi Andy I did do that however the start date is the 1st of April and not 8th of April and the fiscal month ends on the 7th and financial year is from the 8th of April 2020 to 7th March 2021. I did try tweaking your calculation instead of starting on the 1st of Jan I did a dateadd so it starts on the 8th of April this works however when I select a date before the 8th of April it returns 0.
@@shivenappalsamy4506 I had a similar requirement, but April 1st. I created 2 calculated fields to get the start date. I am sure you can combine this into 1 calculation, but it was easier for me to break it up. 1st: Grab the CURRENT YEAR: YEAR([Most Recent Date]) 2nd: APRIL 1ST CY: DATE(DATEPARSE ( "MM-dd-yyyy", "4-1-" + STR([Current Year]) )) Do the same for the previous year: 1st: PREVIOUS YEAR: YEAR([Most Recent Date PY]) 2nd: APRIL 1ST PY: DATE(DATEPARSE ( "MM-dd-yyyy", "4-1-" + STR([Previous Year]) )) You can adjust the "4-1-" to whatever fiscal year start date you want
Really good tip, Andy. I use DATEADD so rarely, but it's really powerful. Having this in the back of my mind helped resolve a problem today.
Used DATEADD to create a hidden reference line (max date plus 2 weeks) to make space at the end of an axis. Allowed me to tidy up labels on a series of line charts so they were beside the last mark instead of risking overlapping the line.
Mark Edwards I use that same technique a lot for spacing but also when I don’t want to see the little pin on the axis.
I honestly don't think I could give this a bigger thumbs up!
One thing to note, I needed this to be a dynamic report that pivots on the current month so I added a filter with a calculation as below
if DATENAME('month',[Date])= DATENAME('month',TODAY()) then 'Y' else 'N' end
and left it on Y. Boom dynamic month based YOY reporting!
I have looked everywhere for simple way to do this...you are a lifesaver!
Thanks for the video Andy. Like Andrzej, I have also been trying to figure out how to do something like this taking fiscal year into account for quite some time. How would I adjust the formulas?
Hi Andy, thanks for the video. Just an observation, I'm not sure why nobody from top comments has noticed, The YoY you got is -ve when clearly sales this year has been greater than PY 😊
Regards,
Mayank
Whoops! I believe the final solution workbook is correct.
@ Andy Kriebel,
In your example, you have used only 1-year data. But I have 5 years of data, and YTD and PYTD is getting added for 5 last years using the same formula.
Can you please let me know how to check only YTD for 2019 and PYTD for 2018.
Brilliant video, exactly what I needed. Please keep them coming 🙏
Hi Andy! Thanks a LOT!!!
I'm new to Tableau so the question may seem simple but anyway...is there any way to compare the period of the current year with the same period of the last year so that the dates are not fixed.
For example, a period of 12 months.
I am also looking for this solution. @andy
Hi Andy, great tip. Love your videos. I have a question, when i do YTD vs PTD calculations i get the right data. My dashboard is set to show this year data, when they select last years data, the Prior year is blank. How do i fix that it picks up last year data anytime a previous year or the last two years are selected to display those KPIs? Please and thank you for everything
Hey Andy. This is awesome. Thnx :) I have a small requirement: between selected date ranges(start date and end date), I need to see the no of records. but I have 3 years of data. so I tried to use start date, end date as date_parameter displayed as a calendar. But the problem is. when I publish this, the end date is not updating to today(dynamically).. can u help me there?
I am getting the error cannot mix aggregate and non aggregate comparisons ...need help!
Hi Andy! Thanks a lot for the video! Can you please help me on the below...
From the above video, instead of choosing the max date i.e. the date is not fixed and I want to calculate PY and CY sales from the range of dates that I have chosen from the date filter... The date range should be dynamic... Could you please provide solution for this!! Thanks in advance!!
Put the date filter in context
Nice Video Andy! Thanks for teaching!
Hi Andy, Great concept! Tried the same with dynamic frequency and dynamic dimension. When I tried fixed calculation inside if or case statement, I am getting a different sum. But when I use it separately , I get the right result. Im just confused . fixed on different dimension inside case/if work differently? im stuck .. Pls help me .. Thank u in advance.
This is the calc I have used..
if [Choose the Dimension] = 'Region' then
{ FIXED [Region] : SUM(if ([Order Date]>=[Current Month/Week/Quarter/Year Start Date ] and [Order Date] =[Current Month/Week/Quarter/Year Start Date ] and [Order Date] =[Current Month/Week/Quarter/Year Start Date ] and [Order Date] =[Current Month/Week/Quarter/Year Start Date ] and [Order Date]
Uh I am just trying to learn Tableau. THis is a varsity course but I am amazed at what can be done and I like your organization.
Great video and explanation! Thank you for this!
@andy While calculating CY Sales it showing error; Cannot mix aggregated and non-aggregated arguments with this functions. Please help
Then that means one side of your calculation is not aggregated. The formulas I used are in the video and in the description. I hope that helps.
Great video, thanks. Is there a way to dynamically control all these four measures. For example; if a user would like to have a drop down month filter and based on the month selection the four fields gets calculated. Thanks in advance.
Yes, but it can get complicated. I’d have to try to reproduce it. Absolutely possible though.
Great video. Thank you
This always shows the most recent month and year in the view.
How would I show different months and years and the same month in the prior year? For example, if the user wants to see June 2022, compare it to June 2021. Would be great to have a month and year filter selection.
You can do that with a parameter. Then update the current year calc to be your month.
@@vizwiz Thanks for the reply. Is this something you can create another video and show us? I am sure everyone would want to have a chose of a month and year selection.
Hi Andy! Thank you for the video! I forgot to mention that the fiscal year at my company starts in July and not January. Since DATETRUNC ignores fiscal years, how would the formulas look different? I apologize, I should have mentioned this in my original post.
Andrzej Szczurko you would have to create another field that has the fiscal start, then ensure the dates are between that date and the other that I showed.
I usually change the financial year to Jul in the data page...works fine my end
@@vizwiz thank you!
@andy, can you please give some more information on how to create the same visual but with FY from July to June. I will appreciate that. Thanks
Andy Kriebel can you please give some more information on how to create the same visual but with FY from July to June. I will appreciate that. Thanks
Thanks a lot Andy!
One question, can this sheet that you created be filtered by your order date?
I think that it is a bit tricky because if you select only a specific order date then you will not have PY Sales.
Any suggestions on this one?
It's definitely possible. Off the top of my head, I think a parameter should be used instead of a filter. Then you create your calcs based on that date.
@@vizwiz but only with parameter and calculated fields?
Can you think of any other possible way?
I can't thank you enough for your videos and your quick response!
This video just saved me a lot of time! Thanks.
Thanks, Andy. Great way to do this! I had a little different approach, but this one works great. I wonder if it's possible to do conditional formatting on the pos/neg ratio (ONLY!) ? I desperately tried to do so and to let automatically color the negative ratios to red and the positive ones to green...I ended up with having just a quick and dirty solution...I calculated the ratios in another sheet which included conitional formatting...and put these 'two tables' in a dashboard at the end. Is there a more handy way to do it?
See if this video helps th-cam.com/video/fwRzMIt_vGc/w-d-xo.html
@@vizwizThanks, but I've seen this before. I had a slightly different demand. At the end, I resolved this by putting three dummy-0 measures for each column in the column shelf so I could format each column separately. The only problem I had was that I couldn't use the measure values as column names, since I had to show this table for orders AND sales and so two have e.g. Ø 2019-title twice. I did the column labeling directly in the text options eventuelly, and it worked well. Thank you for these vids, though. They help a lot to get the idea of tableau, especially when one's used to excel logic.
Thanks, One question - above example is based on reference to current/latest period. If the requirement is to link dynamically to any month period and show comparison as of that month is it possible using this methodology. requirement is to link calculation to filter selection !
Sure, you could create a calculation that is something like [Order Date]
Thanks Andy.
Hi Andy, I was thinking the YoY should have been : sum(cy sales) - sum(py sales).. what do u think?
That gives you change but not % change. It depends on which you want.
@@vizwiz That’s right Andy but my emphasis was actually on subtracting PY from CY (CY-PY) and not the other way round as you did in the video because PY-CY make the interpretation of the increase and decrease misleading.
Ah yes. You’re correct
Hi Andy, thanks for your video, helped a lot!
I was also trying to make the same calculation but not for sales, but for a COUNTD - I’m calculating the number of distinct values in YTD. Unfortunately, it ended up with a mistake of mixing aggregate and non-aggregate arguments. Can you maybe give a tip on that how can such YTD calculations be made?
Many thanks!
My input for YTD CY was:
if [CalendarDay]>=[Jan 1st CY] and [CalendarDay]
It looks like your COUNTD is in the wrong spot. Where you have it now, the countd is calculated inside of the IF statement, thus mixing aggregate and non-aggregate.
Try this -
COUNTD(
if [CalendarDay] >= [Jan 1st CY] and [CalendarDay]
@@vizwiz Thank you!
Please do countD(if [CalendarDay]>=[Jan 1st CY] and [CalendarDay]
andy, is it possible to add background color change depend on the percentage value,,like red if minus or green if plus? I've tried create that but kinda stuck here
Yes, write a calc that simply compared CY vs. PY. Something like CY>PY. This would result in a boolean which you could then put on the color shelf.
Hi, great video!
What about if I want to compare the current data vs the first data?
Then your calc would need to find the first date instead of the previous year. You can use the min function.
Hi,
When I apply these calculations, I am only getting back figures for 1 month (Jan CY) and the same 1 month (PY). I am essentially trying to visualise a dual axis line/area chart that shows PY versus CY. I want to implement the calcs, so they dynamically pull through most current year and prior year as more data is added. Thanks
CY would be { MAX(YEAR([Date Field]) }
PY would be CY-1
Hi Andy, thank you for the reply. If I could ask one more question? I have been tasked with creating a dual axis chart that tracks percentage of total for PY versus CY. I have been able to create LOD calcs that always find the most recent month for the two figures needed to perform the division. However, I’m struggling to find a way to create dynamic calcs that create percentage of total for every month and continue to keep the data always within the most recent 24 month period, especially because two columns are involved to produce each percentage of total across the years. Hopefully this makes sense? Any help would be greatly appreciated. Regards
In date filter, if am changing year suppose we have 3 years data 2017,18,19. and want comparison between 17 and 18. it does not work properly, because in most recent adate we are picking max date and it is comparing with that. Can you suggest anything?
Add the year filter, where you are excluding 2019, to Context. This will make the filter happen before the max date is calculate.
Hi there is there a way to calculate YTD sales depending on the date I have selected in a filter and compare it the the prior YTD sales reflecting the same period? For example 31st July 2019 vs. 31st July 2018…. Many thanks
Definitely possible with a parameter. I'll add it to my list of things to create. Is it urgent?
@@vizwiz thanks for replying. Not majorly urgent but I’d be grateful to watch how you do it. Thank you and great videos, I’m a huge fan.
@@JD_018 Here you go... www.vizwiz.com/2022/04/ytd-vs-pytd.html
Can you help with how this can be done for more than 1 year ... so for example o have data from 2019-21 and want that when the filter says 2020 the previous year should be 2019 or if I say 2021 then it should say 2020
Add the Year filter to context.
How to create YTD as well as quater values in the cross tab?
Beacuse if we add date to column it will have drill down option .
It we keep year it will show year values ..Once we drill down then year will be gone quater wise data only be there...I need both in same view.
2018 YTD q1 q2 q3 q4
Thank you so much for this vid.
How would you approach this if, instead of Sales, you had a calculated field (aggregated) that you wanted to return the current and previous year for?
You can't simply replace Sales with this calculated field because you would be mixing aggregate and non-aggregate things in the IF statement
Struggling with this, manage to figure it out?
What if your "Sales" is an aggregated field already? I'm trying to do this similar summary but PYTD. Any advice?
Struggling with this, maange to figure it out?
Can I bring that down to prior year month, so i can compare monthly YOY trends?
Absolutely you can.
@8.14 what did u do to increase the spacing between the values
Don't I show that?
No you just did some fancy keyboard trick.
Very useful, thank you. I applied the count for SUM aggregates, however I stumble at the YOY and YOY % calculation, where I need to count distinctly the number of order. Hint please!
I used this formula to define the CY orders:
COUNTD(IF [Date Order Request]>= [Jan1 CY] AND [Date Order Request]
@@andreeadaju444 Correct, on the surface, that looks like it should work.
@@vizwiz life saving, thank you!! I'd like to ask for one piece of advice, in the sources there are dates even from april 2021, so for the "Most recent date", I get more than I need. What could I do here? Thanks a million!
I tried this:
Most recent date = if [Now]=
Hi Andy i am huge fan !!! . I have question how would you do this for a fiscal year that starts on the 8th of April i have literally blown my top off trying to figure out a solution
Change the date properties for the data source to fiscal dates.
@@vizwiz hi Andy I did do that however the start date is the 1st of April and not 8th of April and the fiscal month ends on the 7th and financial year is from the 8th of April 2020 to 7th March 2021.
I did try tweaking your calculation instead of starting on the 1st of Jan I did a dateadd so it starts on the 8th of April this works however when I select a date before the 8th of April it returns 0.
@@shivenappalsamy4506 I had a similar requirement, but April 1st. I created 2 calculated fields to get the start date. I am sure you can combine this into 1 calculation, but it was easier for me to break it up.
1st: Grab the CURRENT YEAR: YEAR([Most Recent Date])
2nd: APRIL 1ST CY: DATE(DATEPARSE ( "MM-dd-yyyy", "4-1-" + STR([Current Year]) ))
Do the same for the previous year:
1st: PREVIOUS YEAR: YEAR([Most Recent Date PY])
2nd: APRIL 1ST PY: DATE(DATEPARSE ( "MM-dd-yyyy", "4-1-" + STR([Previous Year]) ))
You can adjust the "4-1-" to whatever fiscal year start date you want
Great video! I have stumbled because my calculations aren't working when I use an LOD. HELP!
IF [Date]>=[First Day CY] AND [Date]
The IF portion of your calculation needs to be moved inside the LOD. It should go inside the MIN.
Hi Andy, Thanks for the explanantion, however i have used the same formula for CY sales and its throwing me an error message
Then you must have an error in the formula. Maybe you're missing brackets or aggregations.
@@vizwiz Hi Andy here is the formula which i am using as per your video: IF [Order Date]>=[Jan 1 CY]AND
[Order Date]
@@vizwiz Let me you know if you want the Tableau file , i will share the file with you on your email if you share your email id .. Thanks !
YOY CHANGE should be new - old not PY less CY
The result should be increase30 % not decrease
My mistake...thank you!
@@vizwiz Great video Andy! I was wondering the same thing as the YOY change looked off. Thanks for the confirmation Susan : )
Thanks Andy. The YoY growth should be positive, no?
Shafeeq Rahiman yes and I added that note in the description.
@@vizwiz oops I missed that, thanks!
Is your Sales Measure a LOD?
Thank you 🙏
Thanks alot bro
Happy to help!