Thanks! This gets complicated if the fiscal year does not start from January. QTD and MTD are a nightmare to build if say your Fiscal year starts from Feb.
Thanks for the post Andy. Our business deals with lengthy financial blackout periods. This solution lets us quickly get an apples-to-apples comparison just by selecting a date.
Hey Andy, instead of sales, lets say I wanted to input an average units per order value, how can I do that without getting the cannot mix aggregate and non aggregate error?
This is helpful! However, I am stuck with how to apply this to FYTD calculation - especially, how to create a field that is the current fiscal year start date, so that I can use that as my lower bound for if [order date]>= current fiscal year start date and [order date]
Solid approach to compare apples with apples when you want to compare the current date with the previous year. I would like to do it with the forecast tho and switching from both?
Hello, how can we handle if its QTD vs previous QTD at selected date? if we use same logic as YTD, 1st quarter gives us 1 and we cannot minus 1 from that. Thus it does not work on every year's first quarter range. How to make counter that last quarter which is 4th is prior from next year's 1st quarter?
This is helpful, I have a similar scenario and pulled out the YTD percentage for the last 3 years, I also wanted to display which records have been newly added/removed along with the YTD calculations, how is that possible
This is great. The challenge I have is exclude the current month and my prior year calculates data including the same time current month. I would like to know how I can do it so it excludes last year the current month.
This video was very helpful, but I have mutliple year data and there are measured values already established. What I need to see is if I run my report on any given date, and I'm using a specific range whether a calendar year or fiscal year ie I'm running a report today, the range of dates are 7.1 - 9.30 - I need assistance with muliple years data - not just 1 or 2 years.
It's based off of LODs, so it'll always use whatever your latest dates are. If you have a newer data source, then the calculations will automatically update.
@@vizwiz Thanks for your response. I have a filter order date yearly. I cant figure it out when ai filter it yearly it doesn't change the prior ytd value. I dont know what calc need.
Hi Andy, I have a situation where the dashboard has to show monthly views of some KPIs and visuals. KPIs are current month(selected month from filter) compared to the previous month %diff. Other visuals are monthly snapshots of sales and profits. I calculated Lookup filter to achieve %diff. However I cannot use it as a global filter across the workbooks. This is preventing me use this solution. Please, do you have any suggestions?
Thanks Andy, I learn new things from every tip that you post here. Can we also do week day to last year week day visual comparison (like saturday week 10 vs saturday week 10 last year).
@@vizwiz so, it will cumulate all the true value multiply by sales? Let's say we have 18 rows which are true, so the function be like this : (row1 × sales.row1) + (row2 x sales row.2) + .... + (row18 × sales.row18)?🙏
Awesome simplicity involved here, love that calculation! Never would have thought about using the boolean like that.
Really slick indeed!
Did a great job of describing CYTD vs PYTD calculations in a short and simple manner! Super helpful video! Thank you
Thank you. It's simple and easy to follow for beginners. Love this!
So glad!
Thank You Andy, this solution works perfectly, very simple and precise. I have seen other solutions online but they are unnecessarily complex
Excellent!
Thanks!
Thanks! This gets complicated if the fiscal year does not start from January. QTD and MTD are a nightmare to build if say your Fiscal year starts from Feb.
Correct. This tip doesn't address that.
Thanks for the post Andy. Our business deals with lengthy financial blackout periods. This solution lets us quickly get an apples-to-apples comparison just by selecting a date.
Fantastic. That’s a great use case.
Great video. Just what i needed. I incorporated this with another video for adjusting to a specific fiscal year. Thanks!
Glad it was helpful!
Hey Andy, instead of sales, lets say I wanted to input an average units per order value, how can I do that without getting the cannot mix aggregate and non aggregate error?
This is helpful! However, I am stuck with how to apply this to FYTD calculation - especially, how to create a field that is the current fiscal year start date, so that I can use that as my lower bound for if [order date]>= current fiscal year start date and [order date]
Solid approach to compare apples with apples when you want to compare the current date with the previous year. I would like to do it with the forecast tho and switching from both?
Hello, how can we handle if its QTD vs previous QTD at selected date? if we use same logic as YTD, 1st quarter gives us 1 and we cannot minus 1 from that. Thus it does not work on every year's first quarter range. How to make counter that last quarter which is 4th is prior from next year's 1st quarter?
You made it easy Andy!
I feel Tableau needs to offer readymade functions to do these calculations. just like how Power BI does it.
Glad it was helpful!
This is helpful, I have a similar scenario and pulled out the YTD percentage for the last 3 years, I also wanted to display which records have been newly added/removed along with the YTD calculations, how is that possible
Thank you Andy. This is exactly what I was looking for.👍
Glad it was helpful!
Thank you for the video. I want count(order id) comparison for the selected date. How can I do that?
Replace the aggregation I have sum of sales I believe, with your countd
Hi @Andy, this is great video and i appreciate it. But how would i go about using Months instead of Specific Date to show the data? Thanks again
Change the date aggregate.
This is great. The challenge I have is exclude the current month and my prior year calculates data including the same time current month. I would like to know how I can do it so it excludes last year the current month.
This video was very helpful, but I have mutliple year data and there are measured values already established. What I need to see is if I run my report on any given date, and I'm using a specific range whether a calendar year or fiscal year ie I'm running a report today, the range of dates are 7.1 - 9.30 - I need assistance with muliple years data - not just 1 or 2 years.
if i have old data source then how to calculate for Previous YTD sales
It's based off of LODs, so it'll always use whatever your latest dates are. If you have a newer data source, then the calculations will automatically update.
So to do prior year mtd and qtd I just need to add another boolean AND statement to the calculated field using month/quarter right?
Sounds about right
What if you want it to filter by year/yearly? is it possible? Thank you
Yes. You would need to add the years you want to filter out as data source filters, preferrably.
@@vizwiz Thanks for your response. I have a filter order date yearly. I cant figure it out when ai filter it yearly it doesn't change the prior ytd value. I dont know what calc need.
@@edmarreyes4956 what’s the calculation you’re using?
Great video! Would you please do a video where the percentage difference font color would change to green if it's positive and red if it's negative?
I did this in my Watch Me Viz yesterday since you asked. 👍🏻
Watch Me Viz - #B2VB 2022 Week 4 - Designing KPIs
ua-cam.com/video/cYVBduPExfo/v-deo.html
Hi Andy, I have a situation where the dashboard has to show monthly views of some KPIs and visuals. KPIs are current month(selected month from filter) compared to the previous month %diff. Other visuals are monthly snapshots of sales and profits. I calculated Lookup filter to achieve %diff. However I cannot use it as a global filter across the workbooks. This is preventing me use this solution. Please, do you have any suggestions?
It’s not working because table calcs are only available in one sheet. You’ll need to have an LOD calc that you can apply across both.
Very helpful and good Explanation 👍
Glad it was helpful!
Thanks Andy
Thanks Andy, I learn new things from every tip that you post here.
Can we also do week day to last year week day visual comparison (like saturday week 10 vs saturday week 10 last year).
Of course. I would do it by getting the day of week number and the year number then create the calcs based on those.
Great tip, Andy! I assume that using the same logic, we can create a start and end date parameter for year over year comparison?
That should work. 👍🏻
THANK YOU SO SO MUCH SIR, this is what i look for TT
but sorry, can I have the explanation about the INT() then multiply by Sales?
INT changes a Boolean (T/F) into 1 or 0. You then multiply that by sales so you either get sales (when true) or 0 when false.
@@vizwiz so, it will cumulate all the true value multiply by sales? Let's say we have 18 rows which are true, so the function be like this : (row1 × sales.row1) + (row2 x sales row.2) + .... + (row18 × sales.row18)?🙏
Not quite. It’ll multiple each true row by 1. So it’d be 1*row1 + 1*row2,etc
@@vizwiz I see..... I got it, sir! Thank you very much. It really helps. Stay healthy and inspire sir😊