This is something I was really looking for. Rather than calculating YoY change though the pill options (table calculation) it's much more robust. Thanks a lot for this helpful content!
Hi Andy , This is super awesome way . I knew that LOD were very capable of doing such a thing , but I tried doing it the same way ended up messing it. Alternatively I have used the lookup function to compare the YOY in my dashboard .
Thanks so much for this video. As I am trying to create my plan and forecasting dashboard for 2021, this video really helped simply my LOD thought process.
Hi Andy, I have just started to use tableau and moving from excel I found it was very difficult to wrap my head around the autoaggregation. I was looking to do something similar and this is the perfect way of doing it. I was concerned I had to do this in Prep through squeal. Your explanation and the level of detail was easy for me to follow. I don't usually comment but you definitely deserve all the credit. Just subscribed! Keep up the amazing work!
Hi Andy! This video is just what I was looking for, thanks! However I'm looking for something more challenging, I did the percent followers difference between the first day of a year and the last day of the same year and my results were OK but when I added my Brand dimension to compare them I noticed that for some brands my last date with followers data was not the last day of the year, for example the last data available was March 3rd. Is it possible to create the same calculation taking into account the last date with available data and not the last day of the year? Thanks.
Andy, this was so helpful to me! However, I ran into a situation where the previous year is "0"....how can I factor that into the equation? Thanks so much for any help you can provide!
Hi Andy, Thanks for the video. It is pretty helpful. I have a question about the 'Alert' calculated field. How can I make it a dynamic field? i.e, instead of giving a hard value of zero, I want to be based on the AVG (YOY change). Is there way to do it. Thanks in advance.
This is very helpful. However, I want to know if there's a way for the previous month (I use month instead of year) to change based on a filter. If anyone knows, please explain or point me to the right direction.Thanks
Excellent video. This solved my YOY problem, but will it solve by MOM (comparing it with the previous month within the same year) for all months? Thanks a lot :)
Hi Andy, I am having similar requirement but having filter of year and YoY should be changed according to filter value. I added filter on year and then added it to context. However, it's not giving prior year sales and hence YoY is also zero. Any guess, what's happening
Hi Andy. this is very helpful. But how can I get the Prior year if I will use relative date as my filter? If I make the filter to this month of current year and compare it to last year of the same month the calculation is not working anymore.
Hi, first off, love the tutorial, very helpful. My only challenge is that this only works for the full year, how would I compare for partial year? For example, if I want to compare sales YTD vs. the same period last year (Jan - Nov 2017 vs. Jan - Nov. 2016)? Thanks
Update - I think I figured out how to run this. For the YTD calculation, nothing changes, but for the Previous YTD, if you only want to include the same months (i.e. Jan - nov 2017 vs. Jan - Not 2017, simply subtract 365 from TODAY(). Using the Superstore data (I created a parameter for "Today" to test it), the calculations would look like this YTD Sales: IF [Order Date]
Andy this is great. How would you look at the same period a year ago. I.e. Look at sales around a holiday (say Father’s Day week before and week after) 2019 VS 2018
Hello Andy, this video was very helpful.. but when I am removing years from the rows, it is showing 0 value in the remaining columns... do you have any solution for this
I had an issue with my YoY calculation because of Leap Year and I would like to share this solution for others who may be having the same issue. DATEADD('year',-1,DATE({MAX(DATETRUNC('month',[Period]))}))
I understand how this works with using MAX date in the dataset. But I have to use date as a filter dropdown. If I selected March 2017 in my filter dropdown, but if the max date in my dataset is May 2017, then this doesn't work. In your LOD calc, I noticed you didn't include anything in the beginning of level of detail calc, so I tried fixing it as { FIXED [Date Dropdown] : MAX(...)}. This allows me to obtain the Month of date selected (in this case, March 2017 instead of May 2017); but then I am not able to obtain the sum(sales) value of Prior month... can you please help ??
Great Tutorial, I used it with thousand of Products to see prior and latest year Profit instead of Region, but One problem, Am i the only one facing performance issue???
Very useful! One question, though...how would you do the same exercise if instead of Full year data you have, for example, data ending in July and you still want to compare latest year to prior? Thanks!
You could find the max date at the monthly level, then use that to total up the current year, then subtract 12 months from it to get the prior year and sum those up.
Hope you can help please...How can I adjust this if my fiscal year starts at April ? I tried changing the default to start fiscal year at April but when I bring in month into the table I lose Jan-mar into another year.....it’s as though it only works from Jan-dec as a year and not fiscal year April- March ..... any help much appreciated 😮
Do the LoD computed fields show up in the dataset ..when they happen at row level as in this case, the first LOD should give TRUE/FALSE at each row level in the source data? I am trying this and it does not seem to show up in the data source
Calculated fields do not show up in the source data. The source data is the data you connect to. A calculation you create in tableau is not in the data you connect to.
dilip1able you should be able to follow along using superstore. I would recommend you do that for your learning. Merely using my workbook won’t help you understand the concepts as much as doing it yourself. Either way, I’ve updated the description with a link.
@@vizwiz Thanks but I guess you have not answered my question please. I understand you subtract 1 from max Year to get Previous year: {max(year(order date))} - 1. However, when calculating YoY change you do this: sum(latest year sales) / sum(prior year sales) - 1, this particular - 1 is what I want to know the reason why Andy🙂.
This is not working when I choose only selected years, for eg: if I select 2014-2016 and my data has 2017, 2018 as well, it's not working. It works only when I select the entire data set.
@@vizwiz I'm still not clear on how to add that filter to context, do you have a sample that I can refer?The problem as I see is that Latest Year is set to MAX(YEAR[Order Date])) ..suppose if I have a filter on year and I choose only years 2014-2015..the Latest Year still shows 2016 instead of 2015. Thank you so much and appreciate your immediate help on this.
if i want calculated about sum(sales -> from November 2014 to Janury 2015 ) / sum(sales -> from November 2015 to Janury 2016)-1 how do i calculated tableau . Please help me
You are amazing person helping everyone to learn the visualisation in a easier way.. Thank you for giving us so much of knowledge. Keep up the good work 😊
Hi Andy, what a great video! Could you post another video on how to compare a same day last week comparison of sales? I'd really appreciate if you would since I have been working around this within a week and met a dead-end. Thanks!
Its awesome channel for Tableau, thanks a lot Andy for sharing your valuable stuff. I have to be more familiar at nested LODs and Please explain internal operation for flowing scenarios: In row shelf: category, Market 1. { FIXED COUNTRY:MAX({ INCLUDE MARKET:SUM(SALES) }) } 2. { FIXED COUNTRY:MAX({ EXCLUDE MARKET:SUM(SALES) }) } Data source: Global Superstore.
For example: if the date range I selected is 1/1/2018-1/31/2018, I would like to see the YoY growth % for the same period last year, 1/1/2017-1/31/2017. or 11/11/2017-2/19/2018 YoY growth % from 11/11/2016-2/19/2017. Would this be possible?
i have a project that requires to create visualizations based on change from 2017 to 2018 to show increase or decrease between the measurements.(this is based on BMI,BLOOD PRESSURE,CHOLESTROL AND A1C) all of which will be seperate .what type of a calculated field will be ideal.
One of the most common question I get is to do year over year based on YTD for current year and last year, especially when entering a new year. I normally pre-process the data in the back-end before loading to Tableau. Do you have any good way of doing that in Tableau directly?
You could use a calc like this and add it to the filters shelf and choose True. This returns on the days that are from the same day of year or less. DATEPART('dayofyear',[Date])
thank you very much for your interresting tutorials. I have one question: for the yoy comparison, is there a way to include only months of prior year that exists in latest year?
Off the top of my head, I suspect you could do it by creating a calc that gets the max month for this year (as a number), then filter the months to those that are less than or equal to that month.
Awesome tutorial! This really helped me, and I can see using many of these components in future reports. For the specific project I am working on I just want to show a text tile on the dashboard with ytd sales total (easy) and the % change (e.g. ytd sales: 12,457,890 +76%), which I think I can figure out how to do from this tutorial (and using techniques learned in some of your other videos), however one caveat, I only want to compare the current YTD sales, against the same period last year (i.e. sales through 7/18/2017 vs. sales through 7/18/2016). Guessing I need to create some kind of filter. Any assistance would be wildly appreciated.
You could use a calc like this and add it to the filters shelf and choose True. This returns on the days that are from the same day of year or less. DATEPART('dayofyear',[Date])
Andy, I want to find top 5 states on( differentiate 12 month rolling average of current month actual data with current month plan data ) Current month top 5 states from : WINDOW_AVG((sum(actual), -11, 0)-sum(plan) Can you help to achieve this?
Хобби
This video has helped me a lot. I wish I could upvote it 1,000 times!!!!
This is something I was really looking for. Rather than calculating YoY change though the pill options (table calculation) it's much more robust. Thanks a lot for this helpful content!
wow you make this so easy. i'm an sql / excel user and struggling with tableau, very thankful to have stumbled upon this.
Hi Andy , This is super awesome way . I knew that LOD were very capable of doing such a thing , but I tried doing it the same way ended up messing it. Alternatively I have used the lookup function to compare the YOY in my dashboard .
Thanks so much for this video. As I am trying to create my plan and forecasting dashboard for 2021, this video really helped simply my LOD thought process.
Hi Andy,
I have just started to use tableau and moving from excel I found it was very difficult to wrap my head around the autoaggregation. I was looking to do something similar and this is the perfect way of doing it. I was concerned I had to do this in Prep through squeal. Your explanation and the level of detail was easy for me to follow.
I don't usually comment but you definitely deserve all the credit. Just subscribed!
Keep up the amazing work!
Thank you William. That's very kind of you.
Wow spent ages trying to figure this out !!! THANK YPU SO MUCH
Nice!! Thanks for letting me know. I’m glad you found it helpful.
Your videos has helped me a lot at work👍
awesome video which helped me solve the problem troubling me for a while, thank you!
Great video Andy
Thanks a lot!
Life saver.
Great explanation!!! Thank you so much!!
Amazing!!!!!!!!!! Thank you!!!!!
gracias bro pase demasiado tiempo buscando esto, muchas gracias!!!
could anyone let me know on yoy change chalculation -1
Hi Andy! This video is just what I was looking for, thanks! However I'm looking for something more challenging, I did the percent followers difference between the first day of a year and the last day of the same year and my results were OK but when I added my Brand dimension to compare them I noticed that for some brands my last date with followers data was not the last day of the year, for example the last data available was March 3rd. Is it possible to create the same calculation taking into account the last date with available data and not the last day of the year? Thanks.
Andy, this was so helpful to me! However, I ran into a situation where the previous year is "0"....how can I factor that into the equation? Thanks so much for any help you can provide!
thankyou
Hi Andy, Thanks for the video. It is pretty helpful. I have a question about the 'Alert' calculated field. How can I make it a dynamic field? i.e, instead of giving a hard value of zero, I want to be based on the AVG (YOY change). Is there way to do it. Thanks in advance.
This is very helpful. However, I want to know if there's a way for the previous month (I use month instead of year) to change based on a filter. If anyone knows, please explain or point me to the right direction.Thanks
How do you do this for fiscal years?
Very nice trick ! Doesn't unfortunately take fiscal years into account ... any tip ?
Excellent video. This solved my YOY problem, but will it solve by MOM (comparing it with the previous month within the same year) for all months? Thanks a lot :)
Hi Andy, I am having similar requirement but having filter of year and YoY should be changed according to filter value. I added filter on year and then added it to context. However, it's not giving prior year sales and hence YoY is also zero. Any guess, what's happening
Hi Andy. this is very helpful. But how can I get the Prior year if I will use relative date as my filter? If I make the filter to this month of current year and compare it to last year of the same month the calculation is not working anymore.
Hi, first off, love the tutorial, very helpful. My only challenge is that this only works for the full year, how would I compare for partial year? For example, if I want to compare sales YTD vs. the same period last year (Jan - Nov 2017 vs. Jan - Nov. 2016)? Thanks
Update - I think I figured out how to run this. For the YTD calculation, nothing changes, but for the Previous YTD, if you only want to include the same months (i.e. Jan - nov 2017 vs. Jan - Not 2017, simply subtract 365 from TODAY(). Using the Superstore data (I created a parameter for "Today" to test it), the calculations would look like this
YTD Sales:
IF [Order Date]
Andy this is great. How would you look at the same period a year ago. I.e. Look at sales around a holiday (say Father’s Day week before and week after) 2019 VS 2018
You need to define those two dates ranges with calculations , then use those instead of my CY and PY calcs.
Hello Andy, this video was very helpful.. but when I am removing years from the rows, it is showing 0 value in the remaining columns... do you have any solution for this
I had an issue with my YoY calculation because of Leap Year and I would like to share this solution for others who may be having the same issue.
DATEADD('year',-1,DATE({MAX(DATETRUNC('month',[Period]))}))
I understand how this works with using MAX date in the dataset. But I have to use date as a filter dropdown. If I selected March 2017 in my filter dropdown, but if the max date in my dataset is May 2017, then this doesn't work. In your LOD calc, I noticed you didn't include anything in the beginning of level of detail calc, so I tried fixing it as { FIXED [Date Dropdown] : MAX(...)}. This allows me to obtain the Month of date selected (in this case, March 2017 instead of May 2017); but then I am not able to obtain the sum(sales) value of Prior month... can you please help ??
Great Tutorial, I used it with thousand of Products to see prior and latest year Profit instead of Region, but One problem, Am i the only one facing performance issue???
Very useful! One question, though...how would you do the same exercise if instead of Full year data you have, for example, data ending in July and you still want to compare latest year to prior? Thanks!
You could find the max date at the monthly level, then use that to total up the current year, then subtract 12 months from it to get the prior year and sum those up.
Hello, thank you for this. I wanted to make this as a BANs. Will this work if I put the order date in the filter?
It should, but it depends on what you want to do with the date filter.
Hope you can help please...How can I adjust this if my fiscal year starts at April ? I tried changing the default to start fiscal year at April but when I bring in month into the table I lose Jan-mar into another year.....it’s as though it only works from Jan-dec as a year and not fiscal year April- March ..... any help much appreciated 😮
Sorry, but I'm not sure of the answer. I'd recommend checking the Tableau Forums to see if it's been asked/answered there.
Do the LoD computed fields show up in the dataset ..when they happen at row level as in this case, the first LOD should give TRUE/FALSE at each row level in the source data? I am trying this and it does not seem to show up in the data source
Calculated fields do not show up in the source data. The source data is the data you connect to. A calculation you create in tableau is not in the data you connect to.
Andy, what can I do, to count the number of rows with a 'true' boolean value? I tried but it's giving 1 value for each row. i want total of 'True's.
Try counting any field and filtering to true
Hi Andy, can we get this twb example file
dilip1able you should be able to follow along using superstore. I would recommend you do that for your learning. Merely using my workbook won’t help you understand the concepts as much as doing it yourself. Either way, I’ve updated the description with a link.
Andy why do you subtract 1 from the previous year sales when calculating the YoY sales.. Thanks
I’m subtracting 1 from the max Year
@@vizwiz Thanks but I guess you have not answered my question please. I understand you subtract 1 from max Year to get Previous year: {max(year(order date))} - 1. However, when calculating YoY change you do this: sum(latest year sales) / sum(prior year sales) - 1, this particular - 1 is what I want to know the reason why Andy🙂.
That converts it to a percentage.
@@vizwiz thanks Andy
This is not working when I choose only selected years, for eg: if I select 2014-2016 and my data has 2017, 2018 as well, it's not working. It works only when I select the entire data set.
If you are filtering out other years, you need to add that filter to context, then you will get the max year of the years that are remaining.
@@vizwiz I'm still not clear on how to add that filter to context, do you have a sample that I can refer?The problem as I see is that Latest Year is set to MAX(YEAR[Order Date])) ..suppose if I have a filter on year and I choose only years 2014-2015..the Latest Year still shows 2016 instead of 2015. Thank you so much and appreciate your immediate help on this.
navin sai google it.
if i want calculated about
sum(sales -> from November 2014 to Janury 2015 ) / sum(sales -> from November 2015 to Janury 2016)-1
how do i calculated tableau . Please help me
You can use the fiscal year from the date dimension options, set the fiscal year as per your requirements and apply those LOD.
I was wondering how to do this for the past 2 days. Then RUclips Algorithm God suggested this to me. ❤
Fantastic! Well done!
You are amazing person helping everyone to learn the visualisation in a easier way.. Thank you for giving us so much of knowledge. Keep up the good work 😊
Wow Andy, I learned the first and the best from you. A great teacher indeed, genius you are.
Hi Andy, what a great video! Could you post another video on how to compare a same day last week comparison of sales? I'd really appreciate if you would since I have been working around this within a week and met a dead-end. Thanks!
Very nice, one of my problems is that I don´t fully understand the level of detail functions. Thanks!
Best use of LOD. Thanks
Its awesome channel for Tableau, thanks a lot Andy for sharing your valuable stuff. I have to be more familiar at nested LODs and Please explain internal operation for flowing scenarios:
In row shelf: category, Market
1. { FIXED COUNTRY:MAX({ INCLUDE MARKET:SUM(SALES) }) }
2. { FIXED COUNTRY:MAX({ EXCLUDE MARKET:SUM(SALES) }) }
Data source: Global Superstore.
Thanks You verry much
display KPI's next to bars which is already done by u, but it's always helps me a lot
Nicely done!
For example: if the date range I selected is 1/1/2018-1/31/2018, I would like to see the YoY growth % for the same period last year, 1/1/2017-1/31/2017.
or 11/11/2017-2/19/2018 YoY growth % from 11/11/2016-2/19/2017.
Would this be possible?
this was helpful, however I need to do this for aggregated measures instead of sales. it does not work for that. pls suggest
Hi Andy, In this LOD how to calculate Latest month if we had last four months data from Nov 18 to Feb 19
Well done
i have a project that requires to create visualizations based on change from 2017 to 2018 to show increase or decrease between the measurements.(this is based on BMI,BLOOD PRESSURE,CHOLESTROL AND A1C) all of which will be seperate .what type of a calculated field will be ideal.
Is there a way you can show us how to build 30 day average 7 day average and show those together in a report and show last 7 days of data
Very useful, thanks ! Just wondering why this is not working when counting IDs (due to agregation) and not sure how to fix it
good question, found any workarounds?
One of the most common question I get is to do year over year based on YTD for current year and last year, especially when entering a new year. I normally pre-process the data in the back-end before loading to Tableau. Do you have any good way of doing that in Tableau directly?
You could use a calc like this and add it to the filters shelf and choose True. This returns on the days that are from the same day of year or less.
DATEPART('dayofyear',[Date])
thank you very much for your interresting tutorials. I have one question: for the yoy comparison, is there a way to include only months of prior year that exists in latest year?
Off the top of my head, I suspect you could do it by creating a calc that gets the max month for this year (as a number), then filter the months to those that are less than or equal to that month.
it works thanks a lot
Awesome tutorial! This really helped me, and I can see using many of these components in future reports. For the specific project I am working on I just want to show a text tile on the dashboard with ytd sales total (easy) and the % change (e.g. ytd sales: 12,457,890 +76%), which I think I can figure out how to do from this tutorial (and using techniques learned in some of your other videos), however one caveat, I only want to compare the current YTD sales, against the same period last year (i.e. sales through 7/18/2017 vs. sales through 7/18/2016). Guessing I need to create some kind of filter. Any assistance would be wildly appreciated.
So, I think I figured it out. I created a T/F calculated field: DATEPART('dayofyear', [period])
You could use a calc like this and add it to the filters shelf and choose True. This returns on the days that are from the same day of year or less.
DATEPART('dayofyear',[Date])
You beat me to it! You method is based off of today, whereas mine calculates the max date in the data set.
Andy, I want to find top 5 states on( differentiate 12 month rolling average of current month actual data with current month plan data )
Current month top 5 states from : WINDOW_AVG((sum(actual), -11, 0)-sum(plan)
Can you help to achieve this?
getting cannot mix aggregate and non -aggregate while creating second calculation filed
wrap it in SUM or MAX
Can we pass parameter to LOD calc?
Yes
Wich software are y ou usine?