1 2 3 Previous Next 44 Replies Latest reply on Jun 14, 2017 7:52 AM by Michelle Ford Go to original post
      • 15. Re: YOY YTD Bar Comparison

        Thanks for feedback, Tom. I am sorry to say that I haven't made any progress on version 3.


        I believe there is a good chance that someone knows how to do this and therefore recommend posting a question in  Forums.  The better the question is, the better the answer is likely to be.


        Posting a Perfect Question  shows various techniques that attract helpers.


        Please ping me if you post a question, because I would for sure like to follow that thread.

        • 16. Re: YOY YTD Bar Comparison
          Tom Scarr

          Great, thanks for getting back to me, I'll let you know how I get on.


          Tom Scarr

          Head of Number Crunching



          Direct Dial: 020 3141 8313

          0845 459 9937


          2 Exmoor Street


          W10 6BD

          www.loaf.com <http://www.loaf.com/>


          Show us some love on Facebook <https://www.facebook.com/Loafhome> /


          / Pinterest <http://www.pinterest.com/loafdotcom/> /




          *"I couldn't be happier.  I lead polar expeditions for a living and spend

          several weeks a year in a sleeping bag in the minus forties, so having a

          nice bed to come home to is something I appreciate more than most!" *said

          Ben S after buying a kingsize Tight Space divan bed


          *"I'm over the moon with our new bed.  It looks great and we love the

          mattress, it still feels like I'm staying in a 5 star hotel!"  *Deirdre M

          said after purchasing a kingsize Darcy bed in natural linen


          *"I am happy, even very happy.  I had a happy ordering and customer

          service, a happy delivery and most of all I had a happy sleep because the

          bed I purchased from you is everything that you said it would be." *e-mailed

          Nathalie B after buying a double Coco French bed


          Really Comfy Beds Ltd t/a Loaf is a UK registered company, number 6455834.

          Registered address: Bridge House, Restmor Way, Wallington, Surrey SM6 7AH.

          This email is confidential and intended for the use of the intended

          recipient only. If you have received this email in error, please inform us

          immediately and then delete it. Unless it specifically states otherwise,

          this email does not form part of a contract.

          • 17. Re: YOY YTD Bar Comparison
            Rebecca Sundquist

            kettan and Tom Scarr, this old thread inspired me to work on kettan's version 3, displaying projected revenue in-line with actual full year revenue. This is a great opportunity to explore table calculations, which I am not familiar with enough yet.


            Knowledge Base:



            First, I'll describe how I displayed projections in-line with actuals. Then I'll discuss the formula I used for projections.




            I used an IIF() function on table calculations to evaluate whether the record is the Current Year or not, so that for Current Year the record displays Projected Full Year Sales, while prior years display actual Sales.


            IIF(Max(Year([Order Date]))=LOOKUP(Max(Year([Order Date])),FIRST())

                , [Projected Sales Full Year]

                , Sum([Sales])



            The next trick is to display the YTD Sales in the foreground as an orange bar and Full Year Sales (projected for 2013, actual for prior years) as a blue bar in the background. Place Measure Names on the Color shelf and Measure Values on the Columns. Then remember to turn "Stack Marks" off. Go to Analysis > Stack Marks > Off. Make sure that the YTD marks are in the foreground by dragging the labels into the appropriate order within the Color legend.


            It is important to note that I had to adjust Full Year Sales on the Measure Values shelf to Compute Using > Pane Down.



            Projection Calculation


            My projection assumes that the remainder of the year will have the same relationship to prior years as what was demonstrated in the year-to-date period compared to prior year-to-date periods. The calculation I used is commonly called a Response Curve in campaign response measurement.


            I used table calculations to identify the average percent complete at 181 days into the year across the past three years (not weighted by total sales per year). Then I divided the current YTD sales by the average percent complete to project the full year sales.


            Avg % of Total Sales:

            ((LOOKUP(ZN(SUM([Sales YTD])), 1)/LOOKUP(ZN(SUM([Sales])), 1))

              +(LOOKUP(ZN(SUM([Sales YTD])), 2)/LOOKUP(ZN(SUM([Sales])), 2))

              +(LOOKUP(ZN(SUM([Sales YTD])), 3)/LOOKUP(ZN(SUM([Sales])), 3))



            Projected Sales Full Year:

            SUM([Sales YTD]) / ([Avg % of Total])


            I appreciate this post about managing the year-to-date period in a live data set: http://community.tableau.com/message/261409#261409, but I just used a hard coded date to keep it simple.

            I created a dashboard that demonstrates the calculation steps and a picture of the Revenue Curve (% of total Sales by day).


            I see your more recent post, but I used your older V2 workbook here.

            kettan, thanks so much for posting this a year ago. It is inspirational because I hadn't been doing anything like this yet, but I will now. Also, I didn't realize that dayofyear was a datepart. Revolutionary! Thanks!


            kettan, your old post lives on two years and counting!

            I am attaching an updated workbook in Tableau 9.0. The great addition is a dynamic Year-to-Date period based on the max order date in the data set. As new orders come in, the max order date will inform the year-to-date period. A Level of Detail expression allows this.


            I also added a detailed walk-through within the workbook to describe the steps and calculations. Please download this packaged workbook to explore. I am eager to see the additional features and efficiencies that the community builds on this workbook.


            I also worked on a version that allowed the user to select the through-date for the year-to-date period via parameter, but I don't think that dynamic parameter defaults are available yet, so I wasn't able to have it default to the most recent order date. I have seen a workbook solve for this by using a parameter that isn't a calendar picker, but instead is a string list of Month + Day values and the default value is "Max Order Date," which would reference the current data through-date. I haven't tried it for myself yet, but there is some inspiration for you.


            I presented this workbook the Kansas City TUG on April 29, 2015.


            Here is an image of the final result compared to results from Tableau's built-in time series forecast. I was happy to find results fell within the confidence intervals. However, I don't expect these calculations to be as reliable when the year-to-date period is only a few days into the year! This exercise was more about demonstrating table calculations than sound projections.


            • 18. Re: YOY YTD Bar Comparison
              Shawn Wallwork

              Rebecca, at the moment we can't reliably include more than 1 image in a post. Your last image is broken. Reply to yourself, and post it again, so we can see it.





              • 19. Re: YOY YTD Bar Comparison
                Rebecca Sundquist

                The broken image in my last post was just a sneek peek at the dashboard in my workbook. (thanks shawnwallwor)



                • 20. Re: YOY YTD Bar Comparison

                  Thank you, Rebecca. This is wonderful. Getting a solution for this hard-to-crack-nut was a pleasant surprise.

                  I look forward to study your work. I am sure many others will too.

                  • 21. Re: YOY YTD Bar Comparison
                    Marcello Granconato

                    Great job Rebecca and kettan quite a useful post!

                    • 22. Re: YOY YTD Bar Comparison
                      Zifeng Li

                      Hi Chris Hobson,


                      You mentioned to use MTD instead of YTD. Can you share the workbook? This is a very good example. Thank you very much Kettan.    

                      • 23. Re: YOY YTD Bar Comparison
                        Sanjiv Lamsal

                        I am trying to recreate YOY YTD Bar Comparision but I am unable to access cube dimension. But I didn't see any of my dimensions from my cube when I try to create new calculated field. Old solutions I saw mentioned about putting those dimensions in Level of Detail shelf but new version doesn't have LoD and I tried putting them under marks..but I still didn't see it.



                        • 24. Re: YOY YTD Bar Comparison

                          I have no experience with cubes and therefore don't know how to help.

                          Hopefully someone else is able to explain why and share a workaround.


                          I think it is more likely to get help if you re-post this as a question in Forums.

                          • 25. Re: YOY YTD Bar Comparison
                            Rebecca Sundquist

                            I have not had success replicating this exercise from a cube data source. These table calcs require OLTP.

                            • 26. Re: YOY YTD Bar Comparison
                              Joshua Milligan



                              When using OLAP, you cannot use dimensions in a calculated field.  This is a limitation based on the multi-dimensional nature of cubes.


                              If you're up to writing MDX (which I'm not ), then you can create calculated members.


                              Here's a good KB article about working with OLAP: Understanding Functional Differences Between OLAP and Relational Data Source Connections | Tableau Software


                              Here's an idea for allowing table calcs to reference OLAP dimensions: http://community.tableau.com/ideas/3281#comment-10603 (I noticed Johan has already back-linked it!)




                              • 27. Re: YOY YTD Bar Comparison
                                Sanjiv Lamsal

                                Thank you kettan and Rebecca Sundquist for your insights. I decided to go with Microsoft SQL Server Table rather than using cubes with Microsoft Analysis Server. I just have to wait till calculated field option feature is supported in Cubes plus some of my users are Mac users and Cubes are not supported in Mac yet. Once I connect with Microsoft SQL Server, everything is working as expected.Untitled3.png

                                • 28. Re: YOY YTD Bar Comparison
                                  Silvio Martinez

                                  This definitely helps.

                                  • 29. Re: YOY YTD Bar Comparison
                                    Bill Immer

                                    This post changed my life!!  Thank you!!