Need help writing calculations like this? Schedule an hour with Eric here: www.eventbrite.com/e/tableau-office-hours-with-eric-parker-tickets-42691995909 Ready to master Tableau? Check out our lineup of upcoming classes! www.eventbrite.com/o/onenumber-15678294163
Thanks, Erik. I'm getting to know little by little this fabulous visualization tool!! Still have a hard time understanding how to create calculated fields, as they don't seem very intuitive to me. Perhaps you could make a video about tips on how to create Calculated Fields? That would be awesome! I just subscribed to the channel so I can browse videos already made by you and be aware of new ones. Thanks again!
Thank you! We will have another video out by the end of the week. I'll work on some more upcoming content around calculations as well and try to do some more entry level videos!
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.
Hey Amitesh. Sure! Instead to the TODAY() function I utilized in the calculations, you can create a date parameter where the user selects the reference date instead.
Thanks for this, Erik!! What would you suggest as a PY calculation if I was looking to apply this to last 6 months sales vs. prior period last 6 months sales?
Hey Caitlyn, no problem. So I think I would do something like IF DATEDIFF('month',[Date Field], TODAY()) > 12 AND DATEDIFF('month',[Date Field], TODAY())
hi ! Thankyou , just a Query : What if we have data for 2022 till February and compare it to 2021 same period, In that case applying today( ) (as 21-May-2022) in the PYTD Field wont work correctly then , Can you suggest something then ?
Hi Varish, thank you for the comment and kind words! Instead of using the TODAY() function for the end point, use the latest date in the data set. You can write the calculation like this { MAX([Date]) } . More on that approach here: onenumber.biz/blog-1/2018/8/15/using-tableau-to-only-display-data-from-the-latest-date
@@onenumbertableau hi I tried this max([date]), but it showed an aggregation error as and the action can't be performed , datepart('dayofyear',max([date])) it shows an error :
Thanks so much for this tutorial, this has been great. I have one question though - could i adapt these calculations to show up until the last day of the last month for both current and previous year? ie comparing a measure for 2023 vs 2024 by month and i would like to see as at end of April for both years what the YTD total was? thanks again, love your channel!
You're welcome! Here's how I'd handle that situation. Replace the TODAY() function with the following: DATEADD('day',-1, DATETRUNC('month',TODAY()) ) That formula will return the final day of last month instead of today's date. Then in the YTD and PYTD you can replace the old references to TODAY() to the formula above.
Hey there - love this video. Isit possible to calculate prior year % growth depending on the date you have selected in a filter? So for example if I have the dates 1st Feb 2020 to 6th June 2020 I want to compare that to the same period in 2019? Many thanks
Of course! The easiest way to handle this would be with date parameters. More on those here: kb.tableau.com/articles/howto/creating-a-filter-for-start-and-end-dates-parameters . Instead of using the parameters in a filter like that article you would use them in a couple calculations. For instance the first calc could be Value, Selected Range. The calc would be, IF [Date] >= [Start Date Parameter] AND [Date] = DATEADD('year',-1,[Start Date Parameter] AND [Date]
Hi Erik, Loved your video. But i am performing the YTD calculations of aggregated measures.I am getting the error of mix of agg/non aggr in the formula. Can you suggest me on how to do it?
Thanks Anudeep! This YTD logic will only work for unaggregated fields. That means you'll probably need to go to a different calculation and apply the logic at that level instead of the aggregated level.
I have one scenario I want to showcase last 12 months from last Max date till previous 12 months of Sales; where each month sales is running sum of previous 12 months. For example: sales of April24 is runningsum of sales from May23 to April 24; similarly May23 sales is runningsum of sales from June22 to May23. How to achieve that?
Hi Arijit, I think doing DATEDIFF of last 12 months on the max date in your data set {MAX([Date Field])} sounds like your best bet. This video may help: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-1MjfxmmPNBk.html&ab_channel=OneNumber-TableauExperts
how to calculate same period last year sales e.g the year start from 1/4/2022 to till date , but i want same data previous year means 1/4/2021 to 7/2/2022
I would probably just use parameters in that case (like this: kb.tableau.com/articles/howto/creating-a-filter-for-start-and-end-dates-parameters). Then you could write a second calculation like DATEADD('year',-1,[Start Parameter]) and do the same for the End Parameter and then filter the previous year values with those calculations.
Hello, I would like to ask. So, I need to calculate cumulative sum for previous date but the previous date doesn't based on today but the date I filtered. This is the formula from SpotiFlyer: Sum([total_amount_rech]) OVER (AllPrevious([trx_date])) / UniqueCount([trx_date]) OVER (AllPrevious([trx_date])) I need to remake that on Tableau. Can you help me to understand the formula in Tableau? Thank you very much in advance.
Hi! I'm not familiar with Spotiflyer but I would recommend using a parameter to select the date and then writing a calculated field like DATEDIFF('day',[Date Field],[Selected Date])
@@onenumbertableau Sir, I wanna ask again. How if I won't make a paramter but date filter? Can I do that stuff? Because I need to make this calculation into a dashboard but the other worksheets are using date filter not the parameter🙏
@@andrea2686 Hmm, not easily. Might be a good question for you to post in the Tableau Forums (community.tableau.com/s/) or Tableau Reddit (www.reddit.com/r/tableau/). Maybe someone there has run into this!
