11 Replies Latest reply on Oct 30, 2018 6:00 AM by Simon Runc

    Slider on Period

    buildstar

      Hi All,

       

      I have a scenario in Tableau to implement a Quarter slider.

       

      The report itself has to initially filter on the MAX(Order Date) as in quarters. So the max quarter is pulled in the report.

       

      Now, I will need to implement a slider that will help me toggle to previous 6 quarters. On report opening the initial view should be of latest quarter but user needs to be provided with an option to use a "Quarter" slider to toggle to previous quarters.

       

      Please refer to the attached workbook and advise on how to implement this solution to the sheet or on a dashboard.

       

      Thanks

       

      Simon Runc

        • 1. Re: Slider on Period
          Alexander Dawson

          Create a calculation to determine how many quarters an order is older than the maximum

           

          DATEDIFF("quarter",

             DATETRUNC("quarter", [Order Date]),

             DATETRUNC("quarter",{max([Order Date])} )

          )

           

          Will return 0 when the order is in the max quarter.  1 for previous quarter and so forth. 

          This however will not max out at 6.

          You could create a slider filter control out of this and default it to 0.

           

          To limit it to 6 there are a couple options.  The first that comes to mind is to create another filter which is not customizable by the end user and boolean such as

          DATEDIFF("quarter",

          DATETRUNC("quarter", [Order Date]),

          datetrunc("quarter",{max([Order Date])} )

          ) < 7

          Do not create a control, but filter that to return only true values.

           

          Not knowing when the most recent order is limits any sort of high level date filter.  If you know you're always going to have orders then you can set Order Date > last 6 quarters.

           

          Hope that helps

          • 2. Re: Slider on Period
            Simon Runc

            So I think this will do the trick...

             

            First I created a dynamic Quarter Index, which counts back from latest quarter (so zero is lastest quarter, -1 is the next one...and so on)

             

            [Quarter Index]

            DATEDIFF('quarter',[Order Date], {MAX([Order Date])})*-1

             

            We can then bring this onto the filter shelf and select 0 to -5

             

            Then we can bring the Quarter onto the Filter shelf, and use the "filter to latest date value...."

             

             

            Expose it, and set to relevant values only...and then change to a slider.

             

            Hope that helps

            • 3. Re: Slider on Period
              buildstar

              Hi Simon,

               

              Apologies for the late reply and thanks for the above sample.

               

              I am attaching the file that I am working on. I have table calculations in my sheet for the QoQ and YoY and also a period toggle. I also have a CQ calculation, which will give me the current quarter values.

               

              It should be default show the current quarter and user needs to be provided option to toggle to previous quarters using a slider. I have used your inputs and it seems to be working fine, but the values do not seem to match. Do I need to update the table calcs ?

               

              Also on YoY the slider need not work. I can work only on the QoQ selection and on a specific quarter.

               

              Please advise Simon Runc

              • 4. Re: Slider on Period
                Simon Runc

                So you want QoQ and YoY calculations...that changes things a bit!

                 

                We can't use the method we used before as when we filter to a Quarter, we are filtering the data to just that quarter, so it don't have access the the previous quarters in order to perform the comparison.

                 

                There are a few ways to do this, one way is...

                 

                By using a Table Calculation as Filter, it has the effect of filtering the view, but not the underlying data (as Table Calculation Filters are applied last in the Calculation Pipeline)

                 

                So first I created a Table Calc version of the Quarter filter

                [Quarter Filter (Table Calc)]

                DATE(LOOKUP(MIN(DATETRUNC('quarter',[Order Date])),0))

                 

                This is set up like this

                 

                and then used as the filter

                 

                Everything then works, when we also set up your Comparison Calculations to work over the Quarter (same set up as the Quarter filter)

                 

                The only downside to this method, is that Table Calc filters can't be applied across sheets, so often aren't the best option when you have a dashboard (multiple sheets) that you want driven by a single filter

                1 of 1 people found this helpful
                • 5. Re: Slider on Period
                  buildstar

                  Thanks Simon Runc

                   

                  This helps. however I am unable to use the filter in case of multiple sheets. I am not getting the option to apply to multiple sheets on a dashboard. Any reason for this ?

                   

                  I have to build similar sheets with dfferent measures from same data source and use that on a dashboard.

                   

                  Also I am not seeing the option to "Select latest quarter" since on opening the dashboard users will need to see the current quarter data as default.

                   

                  Any thoughts ? Please advise. Attaching the updated sample.

                  • 6. Re: Slider on Period
                    Simon Runc

                    I am not getting the option to apply to multiple sheets on a dashboard. Any reason for this ?

                    as per my last post

                     

                    "The only downside to this method, is that Table Calc filters can't be applied across sheets, so often aren't the best option when you have a dashboard (multiple sheets) that you want driven by a single filter"

                     

                    hmm...so this is a very tricky one. The problem is...

                     

                    If we use the actual filter (with it's useful options of default to last date, and apply across multiple sheets) we can't get a comparison (eg. if we filter to Q1 2017, we have filtered out the data for Q4 2016 and so can't do a comparison)

                     

                    If we use a Table Calculation Filter we get round the "access to previous quarters" problem, but we loose the "default last date selection" and "apply to multiple sheets"

                     

                    I don't think we can use FIXED LoDs either, as we don't know which quarter will be selected and so can't create a "previous quarter" value, in order to do the comparison.

                     

                    The 2 solutions I can think of here are...

                     

                    1. Use a parameter to select the quarter, although we'd have to label the parameter, something like, 0,1,2,3,4,5 meaning 0 = "Current Quarter", 1= "1 Quarter Back"...and so on. You could create another sheet which would display the "quarter selected", but wouldn't be on the slider

                     

                    2. The other option, and one I'd go for, would be to pre-compute the variances in the data. So you'd have your "Actual Sales" column, as is and then create 2 more. One would be "Previous Quarter", and the other would be "1 year back" and the parameter QoQ or YoY would choose which one was shown. By doing this in the data, you can get the "Previous" and "1 year back" in the same row as the quarter you want to filter on. At a high level the table would look something like this

                     

                     

                    This way when you select 2017 Q3, for example, you can just have SUM(Sales)/SUM(Sales - Previous Qtr)-1 for the Variance (for QoQ)

                     

                    Hopefully you have the freedom to pre-compute the levels in the data

                     

                    Hope either of those work, and make sense.

                    • 7. Re: Slider on Period
                      buildstar

                      Thanks Simon Runc

                       

                      a parameter to select the quarter, although we'd have to label the parameter, something like, 0,1,2,3,4,5 meaning 0 = "Current Quarter", 1= "1 Quarter Back"...and so on. You could create another sheet which would display the "quarter selected", but wouldn't be on the slider

                       

                      How do I implement this ?

                      • 8. Re: Slider on Period
                        Simon Runc

                        So here you go...

                         

                        I first adapted our Index Calculation, and renamed it "Quarters Back"

                        [Quarters Back]

                        DATEDIFF('quarter',[Order Date], {MAX([Order Date])})

                         

                        I also set up a parameter with the values 0-5 on a slider

                         

                        We can then use the parameter and the above field to tag the 2 quarters we need

                        [Quarter Selected and Comp]

                        IF [Quarters Back] = [Select Quarters (0 = Current, -1 Previous...etc.)]

                        THEN 'Current'

                        ELSEIF [Period] = 2 THEN

                           IF [Quarters Back] = [Select Quarters (0 = Current, -1 Previous...etc.)]+1 THEN 'Comp' END

                        ELSEIF [Period] = 1 THEN

                            IF [Quarters Back] = [Select Quarters (0 = Current, -1 Previous...etc.)]+4 THEN 'Comp' END

                        END

                         

                        and then we can then bring this onto the canvas (filtering out nulls) and then we use the "hide" option to hide the "comp" value (we don't want to see it, but need it to perform the YoY/QoQ calculation, which is what hide does)

                         

                        And that's it really. The 'how it works' shows what the calcs and parameters are doing and the 'parameter solution' sheet shows how the final table looks. This already has the actual quarter in the Row, but you could have another sheet which (depending on the parameter selection) just shows the selected quarter (it's just the "current" value from our [Quarter Selected and Comp] field)

                         

                        Hope that helps

                        1 of 1 people found this helpful
                        • 9. Re: Slider on Period
                          buildstar

                          Awesome Simon....this helps !

                           

                          Thanks

                          • 10. Re: Slider on Period
                            buildstar

                            Hi Simon, Simon Runc

                             

                            Have another question to above. The slider works fine when the QoQ option is set and works perfectly with change in quarters in the slider.

                             

                            Now when it is set to YoY and we use the slider it needs to compare the value with same quarter in previous year, same holds good for the different quarter selection in the slide -1,-2,-3 etc.

                            Can you please advise how this can be changed in the logic ? Since currently the slider works fine for YoY only when set to 0 in the slider.

                             

                            Thanks

                            • 11. Re: Slider on Period
                              Simon Runc

                              I think you just need to remove the [Quarters Back] from the filter. Due to the orginal spec we created this to filter to only the last 6 quarters, but we don't really need this now as the parameter restricts how far anyone can go back. By having this in, when the slider is set to 2 (or more) it doesn't have the historical quarter to do the comparison, so removing this filter gives it access to all previous quarters.