#Tableau
Вставка
- Опубліковано 18 кві 2022
- In this tip, I show you how I calculate year over year change based on a date selected with a parameter. The use case is to calculate YTD sales depending on the date selected and compare it to the prior YTD sales for the same period.
Download the workbook here - www.vizwiz.com/2022/04/ytd-vs... - Наука та технологія
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!
Great video. Just what i needed. I incorporated this with another video for adjusting to a specific fiscal year. Thanks!
Glad it was helpful!
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.
Thank you Andy. This is exactly what I was looking for.👍
Glad it was helpful!
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.
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]
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 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
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?
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.
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.
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?
Very helpful and good Explanation 👍
Glad it was helpful!
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!
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.
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.
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
Thanks!
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😊
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.
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?
Is it possible to create a calculated field that gets the sum of sales for all time? So as in the sum of sales from now to as far back as the data goes? I'm trying to subtract sales from all time minus sales of YTD
{ SUM([Sales]) } would give you total sales
@@vizwiz Thank you very much!
You’re welcome. Thanks for watching.
@@vizwiz One more question do you know how to make a ytd calculated field a COUNTD type of variable so lets say it was something like Customer id or Order id? I'm trying to know the exact amount of customer ids were in this year vs the prior year.
@@johnshecut3016 probably something like
If year(date) = {max(year(date))} then product_id end
That will look at each row and if that row is the current year, it’ll return the product. Then you can wrap set the aggregation to a countd.
How do you do month year date to previous month year date
What’s month Year date?
@@vizwiz That's what I'm saying lol. But what I figured out is good for now but what they were wanting was YTD vs Prior YTD but only between January and May and I was able to do it. Your helped enough thank you
@@vizwiz And thank you for responding as well
@@johnshecut3016 No problem
@@vizwiz Is it possible to still have a year filter if your doing YTD vs PYTD calculations. For example you create the YTD vs PYTD calculation but you want there to be a year filter so you can choose a specific year and compare it to that previous year.