1 2 Previous Next 27 Replies Latest reply on Jan 9, 2015 12:36 PM by Shawn Wallwork Go to original post
      • 15. Re: Overlay lines for two time periods
        Chris McClellan

        Hi Joe, I'm following along just to learn (I was trying to answer Emily's question the other day, but I still can't find the right answer).  Is there any chance you can publish your twbx file so I can see how it works please ?

        • 16. Re: Overlay lines for two time periods
          Joe Oppelt

          Correct.  You don't see anything for actual 2014 data because no 2014 data is in there.

           

          As for wanting to see data for past ranges, consider using the parameter idea I offered above.  Here it is again:

           

           

          you could have a parameter that lets the user select a year.  Then, instead of comparing year([order date]) to today, you would say

           

          If year([Order Date]) = [param_year]-1 THEN dateadd('year',1,[Order Date]) else [Order Date] end

           

          ... and then you could filter the sheet for year([calculated date 1]) = [param year].  And then report on that.  (Or use your slider.  That should work OK too.)

          • 17. Re: Overlay lines for two time periods
            Joe Oppelt

            I'll publish something tomorrow.  I don't have tableau installed where I am now.

            • 18. Re: Overlay lines for two time periods
              emily.arbuckle

              Hi Joe,

               

              Thanks for your continued help here. I tried the parameter suggestion above but it still doesn't seem to be working - I can't see any overlay for a past time period. I don't have a lot of experience with parameters so may have messed something up in that configuration. I've attached the workbook again here - would you mind taking a look?

               

              Thank you!

              Emily

              • 19. Re: Overlay lines for two time periods
                kettan

                I wonder if an extra unified date dedicated for the time axis and filtering would be a suitable solution?

                 

                The formula [Order Date Unified] uses purposely a leap year even if Superstore has no orders on February 29th.

                 

                DATE(DATEADD('year', 2000 - YEAR([Order Date]), [Order Date]))
                
                
                
                
                
                
                

                 

                See more in attached workbook.

                 

                thread 153215 Overlay lines for two time periods.png

                 

                Since you are on a lower version (8.0) than I (8.2), I modified the twb xml with the hope you can open it.

                See attached with suffix 8.0.

                • 20. Re: Overlay lines for two time periods
                  Joe Oppelt

                  Right click on Param_year parameter you created.  Click EDIT.  In the middle of the dialog box are radio buttons for "allowable value".  Click LIST.  Now click down in the box below it where it says "Click to add new values".  Give it some valid values (one per line.)  2010, 2011, 2012, 2013, 2014, for example.  Click OK.

                   

                  Now right click on that parameter again.  Select "Show Parameter Control".  This will display the parameter for the sheet.  You'll see it appear below the Calculated Date 1 filter slider.  Hover over the title of the displayed parameter.  You'll see a little down-arrow in the upper right of the parameter.  Click it.  Change the display of the parameter however you want.  (I like Single Value List.)  Now the user can select a year.  (The way I will describe this will use the selected year as the "base" year, and the display will show the base year and the prior year.)

                   

                  (You can get the comma out of the paramater display by right clicking on the parameter in the left side bar, select "Default Properties" and then select "Number Format".)

                   

                  Try selecting 2013 in the parameter on the sheet.

                   

                  [Your Calculated Date 1] will now take any data from 2012 (or the year prior to the selected year from the parameter if you select an other value) , and add 1 to the year in the calc field.  If you select a slider range for any date from 2012 through 2013, you will see 2012 and 2013 data lines.  In fact, if you select only 2013 date values in the slider, you'll still see 2012 and 2013 values.  (That's because there will be no 2012 values in [Calculated Date 1].  They will have been modified to 2013.)  You can see a visualization of what's happening in [Calculated Date 1] by selecting 1/1/2011 through 12/31/2013 in the slider and seeing how that looks.  You'll see your 2011 data on the left of the chart, and your 2012 and 2013 lines on the right of the chart overlaying each other.

                  • 21. Re: Overlay lines for two time periods
                    Joe Oppelt

                    What Kettan has done in his example is similar to the suggestion I made with the calc field that looks like this:

                     

                    Consider this calc:

                     

                    If year([Order Date]) = year(today())-1 THEN dateadd('year',1,[Order Date]) else

                    If year([Order Date]) = year(today())-2 THEN dateadd('year',2,[Order Date]) else

                    If year([Order Date]) = year(today())-3 THEN dateadd('year',3,[Order Date]) else

                    [Order Date]

                    end

                    end

                    end

                     

                    ...only he did it with one neat calc instead of all the "IF"s.  (He edited the format of the resulting range (using the "default format" option) so that it only displays MMM D without the year.  That's why you see only months and days displayed in the slider.)  All data lays along the same time range.  Then the user will select the year (or years) he wants to see compared.  And because of the slider, the data will be limited to the range selected.

                     

                    It's all a similar underlying principle (lay the data along a common date axis).  It's just a different approach.  My approach calls for the user to select one year to examine, and the calcs will automatically grab the prior year.  Kettan's calls for the user to select two years if he wants to see two years.

                    • 22. Re: Overlay lines for two time periods
                      Joe Oppelt

                      Chris -- The example Kettan uploaded approximates what I have in my own workbook.  I'll defer to that one bcause it will save me the time of anonymizing my workbook to upload here.  This is my modified version of Emily's workbook, also aproximating what I have in my own.  It's a version 8.2 workbook.

                      • 23. Re: Overlay lines for two time periods
                        Shawn Wallwork

                        Kettan, ever since you posted this I've been playing around with your order date unified calc/system. I think it's fascinating. But I won't go into all that here (I'll save if for a Tableau Tinkering post). I've taken the liberty of 'improving' your formula a bit to allow for different FY start months; accommodating the original questioners need (FY starting in November).

                         

                        Anyway here are the working parts:

                         

                        Adjust For Fiscal:
                        DATEADD('month', [Fiscal Year Start], [Order Date] )
                        //where Fiscal Year Start is a parameter
                        
                        Order Date Unified:
                        DATE(DATEADD('year', 2010 - YEAR( [Adjust for Fiscal]), [Order Date]))
                        
                        
                        
                        

                         

                        See attached. Here's a screenshot of the results:

                        Unified Dates.png

                        Johan, I really do think this is an interesting discovery of yours and could be very useful for lots of Date-over-Date needs.

                         

                        Nicely done.

                         

                        --Shawn

                        • 24. Re: Overlay lines for two time periods
                          kettan

                          Thanks for kind words, and thanks for adopting and improving this date axis solution.

                          I look forward to read more about it when posted in  Tableau Tinkering.

                           

                          Ps. I just made a reference to the "unified date" solutions in  Re: Plotting Data From Current Year and Previous Year on Same Graph.  If the "unified date" solution helps questioner of mentioned thread, then probably many others – having asked similar questions – would also benefit.

                          • 25. Re: Overlay lines for two time periods
                            kettan

                            To whoever likes to include year-over-year comparisons in Tableau's butter zone,

                            please consider up-voting

                             

                            Month and Day combined (MD) as an option in the date hierarchy

                            Add a Month/Day custom format

                            • 26. Re: Overlay lines for two time periods
                              kettan

                              Here is a workaround for another yearless date axis issue, namely per week number and day.

                               

                              Re: Day of Week vs. Day of Week in Previous Years

                               

                              I share it here because 1) I think you might be interested and 2) so I easier find it if needed.

                               

                              That said, I am not a strong believer in the value of week over week comparisons. Therefore I didn't create this as an idea and thus continuing  the value of good forum questions campaign.

                              • 27. Re: Overlay lines for two time periods
                                Shawn Wallwork

                                Here you go my friend: Tinkering with Johan’s Unified Dates Calc « Tableau Tinkering

                                 

                                Let me know if I got anything wrong.

                                 

                                Cheers,

                                 

                                --Shawn

                                1 2 Previous Next