10 Replies Latest reply on Aug 8, 2016 6:11 AM by Simon Runc

    Using a date parameter to update YTD figures.

    Ray Mullins

      Hi all,

       

      I have a question relating to parameters and YTD results that I have been unable to find a solution to online. All help is appreciated!

       

      The scenario is pretty simple I have the following:

       

      - A date parameter to flick from quarter to quarter in 2015 and 2016. Each quarter selection will tell me the total sales for that quarter.

       

      - What I hope to do, is include a further display of information that will keep a YTD running total of sales. i.e. when I select Q2'16 using the same parameter it will sum Q1+Q2 and show this total.

       

      - As a further complication I would like to be able to use it for 2015, and clearly the running total for when we move to 2016.

       

      Is this possible using 1 parameter, as I really don't have room on the dashboard to include a 2nd parameter.....

       

      Thanks,

       

      Ray.

        • 1. Re: Using a date parameter to update YTD figures.
          Simon Runc

          hi Ray,

           

          Yes pretty sure we can achieve this...Can you let me know what form your parameter takes? is is a string 'Q1 2016', 'Q2 2016'...etc. or are you just letting the user select the quarter, so Q1, Q2, Q3, Q4?...or something else?

           

          The key here is to extract the Quarter number from this entry (which we can do in a multitude of ways!...for example if it was like my first guess INT(MID('Q1 2016',2,1)) would return the value 2)...once we have this, we can create a formula is the form of

           

          IF DATEPART('quarter', [Date]) <= [Your Parameter Quater] THEN [Sales] ELSE 0 END

           

          So this will give us a field that only contains values prior to the selected quarter for both years. If you bring your years and this field into the pane it will show YtD (to selected quarter) for any years in the viz. It's hard to be more precise, as I don't know what your final dashboard looks like, but hopefully the above (from a logic perspective) makes sense.

           

          If not, or you can't get it working, can you mock something up in Superstore (or an anonymised version of your workbook) and I'll add the relevant calcs in.

          • 2. Re: Using a date parameter to update YTD figures.
            Ray Mullins

            Hi Simon,

             

            Thank you for your reply and apologies for the delay in responding.

             

            My parameter takes the form of '2016 Q1'.

            The settings within the parameter itself use:

            Data type: Date

            Display format 2016 Q1

            I use a range of values for 2015 and 2016 where quarter is my step size.

             

            How would you write a formula to extract the quarter info in this instance? I think I can follow through the rest of the logic, sorry its all brand new to me :-)

             

            Thanks for you help.

             

            Ray.

            • 3. Re: Using a date parameter to update YTD figures.
              Simon Runc

              hi Ray,

               

              So if your parameter is a date-type then you can use the DATEPART function. This extracts just a part of the date (so the Quarter, Month...etc.).

               

              So for example DATEPART('Month', #01/01/2015#) returns 1

              as does DATEPART('Month', #01/01/2016#)

               

              In this way you can use this to bring back just that month for any years. So if you have data for 2015 and 2016, and a parameter that was choose month (where the user had selected 3, aka March) then

               

              IF DATEPART('month', [Date]) <= DATEPART('month', [Parameter Month Select]) THEN 1 ELSE 0 END

               

              and bringing this field onto the filter shelf and setting to 1, would only return data from Jan - March for 2015 and 2016.

               

              So similarly you can just DATEPART('quarter', [Your Parameter]) to extract just the Quarter Number.

               

              If it had been a string, you could use the string functions RIGHT, LEFT, MID to get just part of it. For example LEFT('2016 Q2', 4) would return '2016' (the 4 right most characters).

               

              Hope that does the trick, but let me know if not.

              • 4. Re: Using a date parameter to update YTD figures.
                Ray Mullins

                Hi Simon,

                 

                Thank you so much for your help. Rather than keep coming back to you on questions, I have taken your advice and packaged a workbook with the bare essentials to make this work. If you had the time I would very much appreciate your help.

                 

                Thanks again - I owe you a pint :-)

                 

                Ray.

                • 5. Re: Using a date parameter to update YTD figures.
                  Simon Runc

                  hi Ray,

                   

                  Please find attached. So you needed to use DATEPART and not DATETRUNC (DATETRUNC has both the Year and TRUNC part, where as DATEPART brings out only that part). You also needed to apply the DATEPART to both the Date field and the Parameter.

                   

                  In the attached I've created 2 filters, one for Selected Quarter, and one for Selected Quarter YtD.

                   

                  Hopefully that makes sense, but please post back if not.

                  • 6. Re: Using a date parameter to update YTD figures.
                    Ray Mullins

                    Thanks Simon, I am going to use that on my dashboard. I will use 2016 only for now and perhaps send back a screenshot of how I hope it will all look together - I may look at the 2015 cumulative totals and how to reset that filter as we roll back through the quarter.

                     

                    Thanks again for your help.

                     

                    Ray

                    • 7. Re: Using a date parameter to update YTD figures.
                      Ray Mullins

                      Hi Simon,

                       

                      Any chance you could have a look at the v.2? I thought I could make your original answer work, but if I could get the info appearing as 1 figure.

                       

                      i.e. total accumulating for 2015, resetting to zero in 2016 and accumulating again based on use of the parameter.

                       

                      I have included in the dashboard the figure I would hope to see based on what quarter is selected to help out.

                       

                      Thanks,

                       

                      Ray.

                      • 8. Re: Using a date parameter to update YTD figures.
                        Simon Runc

                        hi Ray,

                         

                        So for a cumlative measure you need to use RUNNING_SUM (which Tableau has kindly put as a quick table calc option). In the attached I've shown this using the parameter, and just a regular filter (as you don't need to pull just the quarter part, but are looking as Date <= Selection you could just use a regular filter.

                         

                        So to set up the running sum, we click on the total sales measure and select 'Running Total'

                         

                         

                        Running Total is a Table Calc, and so you can determine how it's calculated (i.e. over what dimensions). By Default Tableau sets this to Table Across, but we don't want that here (that would create a running sum across years for each quarter). We want to calculation to run table down, so we change that here

                         

                         

                        hope that helps, but please post back if that doesn't make sense.

                        • 9. Re: Using a date parameter to update YTD figures.
                          Ray Mullins

                          Hi Simon,

                           

                          99% there, I spent a few hours yesterday trying to display the running total as a "single value".

                           

                          My dashboard has extremely limited space, so I can just about allow for the parameter to decide the quarter, and the running total applicable to that quarter to display as a single value. Is that possible? I couldn't see an option for this anywhere.

                           

                          Thanks again Simon.

                          • 10. Re: Using a date parameter to update YTD figures.
                            Simon Runc

                            hi Ray,

                             

                            So if you just want the total, you can just remove the Quarter from the VizLoD...and once we do that we don't really need the running sum anymore. Tableau is all about the VizLoD...I think you might find my quora answer on the different calculation types and how they relate to the VizLoD useful Answer - Quora

                             

                            Quick .gif below showing what you need to do...

                             

                            Remove VizLoD.gif