1 2 Previous Next 23 Replies Latest reply on Jun 26, 2018 9:46 AM by Justin Jarmon

    Date Filters, Calculations and Parameters

    Justin Jarmon

      Hello all:

       

      I am fairly new at Tableau, so please excuse my lack of knowledge.

       

      I am creating a dashboard for the Customer Service department of my organization and have date issues. Here's my issue:

       

      Our department would like the representatives to have two different options for selecting dates.

       

      1. Date Range - parameter that allows them to choose specific ranges. (i.e., Most Recent Week, Last 4 Weeks, and Last 52 Weeks)

           i. I have a Time Filter formula that shows/hide to work with a date filter I have in place. (Please see the attached)

       

      2. Date Filter - This is the filter that pulls up like a calendar.

       

      Questions that I have:

       

      1. Is it possible for the two filters to work together? Meaning, if I were to select last week in the "date range" the date filter would reflect the dates.

       

      Our data for this particular dashboard updates on Thursday evenings and with new data available on Friday.

       

      Is it possible to always reflect the last two weeks of data between Monday through Thursday, and on Friday reflect the most recent week of data?

       

      If this is confusing let me help you out. This is a bonus structure to reward our employees that perform well on a weekly basis. They are paid two weeks in arrears which means data for them to view will be two weeks in arrears until Friday.

       

      Example: This week's data is reflecting the week of 6/4/2018. On Friday it will reflect the week of 6/11/2018. As of now, I manually update my time filter calculation for the "most recent week" to change between 1 or 2 depending on the date.

       

      PLEASE HELP!!!!

        • 1. Re: Date Filters, Calculations and Parameters
          Joe Oppelt

          Your question is very broad.


          The short answer is YES.  You can do all those things.  But I suspect you don't mean to ask just that yes/no question.

           

          Some of it will take some level of expertise.  Some of it is rather basic.  I hesitated to respond here, because I can see myself getting wrapped up in a week-long dialogue of design and consultation with you.

           

           

          At a minimum you're going to need to upload a workbook that I can show you some things with.  We can start with popping between the 2 different date selection methods.

          1 of 1 people found this helpful
          • 2. Re: Date Filters, Calculations and Parameters
            Justin Jarmon

            Thanks Joe!

             

            I am uploading the workbook.

            • 3. Re: Date Filters, Calculations and Parameters
              Joe Oppelt

              First problem.  You're on Version 10.0.  I can work in any version 10.1 or higher.  But anything I do in those versions will not be accessible to you on 10.0.


              Can you get another version installed on your computer so we can work together?  (You can have multiple versions installed at the same time, so you'll still be able to do what we come up with in your 10.0 application.)

              • 4. Re: Date Filters, Calculations and Parameters
                Joe Oppelt

                I have started doing stuff in V10.1.  Since you can't open it, I'll post calcs I've made.

                 

                I made two new parameters.  Start Date and End Date.  Users will select start- and end-dates in these if they want to do custom dates.  they're just basic type-in date parameters, and if you click in the type-in box, the calendar pops up.

                 

                Then I made these two calcs:

                 

                [Start Date Range]

                 

                DATE(case [Date Ranges]

                when "Most Recent Week"

                   then DATETRUNC('week', TODAY())

                when "Custom Dates"

                   then [Start Date]

                when "Last 4 Weeks"

                   then Dateadd('week', -3, DATETRUNC('week', TODAY()))

                when "Last 52 Weeks"

                   then Dateadd('week', -51, DATETRUNC('week', TODAY()))

                When "YTD"

                   then datetrunc('year', Today())

                END)

                 

                 

                [End Date Range]

                 

                DATE(case [Date Ranges]

                when "Custom Dates"

                   then [End Date]

                else today()

                END)

                 

                 

                Depending on what the user selects, the proper start- and end-dates for filtering will get determined with these two calcs.

                 

                Finally one more calc.

                 

                [Use This Date?]

                 

                if [Add Ts] >= [Start Date Range] and [Add Ts] <= [End Date Range] then 1 else 0 END

                 

                (I made this calc in the CSD_PBM data source using the [Add Ts] date field.  You'll want to make these three calcs in any of your data sources, using whatever date field you need to filter on.)

                 

                I made the [Use this Date?] calc a dimension.  You can then put this calc on FILTERS, and select for value = 1.  You'll get only the rows that fit the range.  I made a simple sheet for testing purposes, displaying only the [Add Ts] values (as Exact Date) and the [Use This Date] calc next to it on Rows.  Play with the parameter, and you can see where the 1s and 0s land.

                 

                Notice that the Start Date and End Date parameters don't come into play here unless you select "Custom Dates".  Once we get to a point where we can share workbooks, I can show you how to display the Start- and End-Date params on our dashboard only when they are relevant.


                So this is a start.

                • 5. Re: Date Filters, Calculations and Parameters
                  Justin Jarmon

                  Joe,

                   

                  I am working on updating my version. We have updated versions available and have reached out to our IT department.

                   

                  This is great! I am going to use the information above until we are able to share workbooks.

                  • 6. Re: Date Filters, Calculations and Parameters
                    Justin Jarmon

                    Joe,

                     

                    I have updated my version of Tableau.

                     

                    I have questions regarding the parameters and calculations that you have created. I will wait until I hear back from you.

                     

                    Thanks for all your help!

                    • 7. Re: Date Filters, Calculations and Parameters
                      Joe Oppelt

                      V10.1 attached.  If you have a higher version, we can move to that.


                      I'm working in Sheet 32 for now.  (I'm using a new sheet to have something very simple for now.  We can move to the complicated pie charts, etc. when we have things working the way we want.)

                      • 8. Re: Date Filters, Calculations and Parameters
                        Justin Jarmon

                        I am using 10.5 now.

                        Please see attached.

                        • 9. Re: Date Filters, Calculations and Parameters
                          Joe Oppelt

                          Next, in the attached I added a dashboard to display sheet 32.  See Dashboard 8.  On here I have the two date params, and an empty container floating on the dashboard.


                          See Dashboard 9.    I dragged the two params into their own container.  I also made a "popout" sheet.  (Go to the sheet.  Play with the Date Range param.  This sheet only displays when we have "Custom Dates" selected. The contents of the sheet are meaningless.  It just exists to display or hide conditionally.)

                           

                          On Dashboard 9 I have dragged the pop-out sheet into that empty container.


                          Dashboard 10.  I dragged the parameter container into the container with the pop-out.  Now play with the Date range param.  Now you see why I called the sheet "pop out".

                           

                          On Dashboard 11 I positioned it to float beneath the container where the data sheet lives.  (I colored it grey so we can see the container.)  You can hide pop-outs behind just about anything.  But now you can see that the date params only display to the user when "Custom Dates" is selected.  Any other selection relies on TODAY() and will not need the date params, so I hide them.

                           

                          That's the next step for now.

                           

                          I have upgraded my workbook to 10.5.  (I saw your latest message while I was typing this.)

                           

                          So at this point we have a mechanism to select the proper rows based on the user's choice of Date Ranges, and I mess around with displaying/hiding things when appropriate.

                          1 of 1 people found this helpful
                          • 10. Re: Date Filters, Calculations and Parameters
                            Justin Jarmon

                            Joe,

                             

                            YOU ARE A LIFE SAVER!!!

                             

                            This is what I needed. I am going to review each Dashboard, param and calc in detail to make sure i fully understand.

                             

                            THANK YOU!!!

                            • 11. Re: Date Filters, Calculations and Parameters
                              Joe Oppelt

                              OK.

                               

                              And regarding how to display x-many days if it's Thursday and y-many days if it's Friday, you can use the DATEPART() function to see what day is today.

                               

                              DATEPART('weekday', TODAY())

                               

                              Returns an integer value of 1-through-7 (corresponding to Sunday through Saturday.)  So if the value is 6, it's Friday.  If it's 2, it's Monday.  Etc.  Based on that, you can create your viz to show x-many days or y-many days.  We can cross that bridge when it's time to do that.

                              • 12. Re: Date Filters, Calculations and Parameters
                                Justin Jarmon

                                Everything looks good.

                                 

                                How would we get this on the main dashboard which houses the difficult pie chart?

                                • 13. Re: Date Filters, Calculations and Parameters
                                  Joe Oppelt

                                  Let's first work with the donut chart sheet.

                                   

                                  You have a pile of filters on there, and I don't want to reverse engineer to figure out what they all do.

                                   

                                  Take off all the filters that mess with dates (and tell me about any calc fields used as filters that also involve dates in the calcs.)


                                  What we need to do is rely on the [Use this date?] calc to be our date filter.  Nothing else.  No problem filtering by agent or area or other dimensions.  I'm only concerned with limiting date filtering to rely only on [Use this date?].  Also, I shoved [Add Ts] into [Use this date?].  What is the date that we really want to be filtering by?  [Use this date?] needs to be modified to use that date if it's not [Add Ts].

                                   

                                  Can you get [Use this date?] to filter this sheet the way you want?

                                  • 14. Re: Date Filters, Calculations and Parameters
                                    Justin Jarmon

                                    Custom Dates is the only date filter that we've been using. The Time Filter calc is being used to provide the different date ranges.

                                    if [Date Ranges] = "Custom Dates" then "Show"

                                    ELSEIF  [Date Ranges] = "Most Recent Week" and DATEDIFF('week',[Custom Start Date],TODAY()) <= 2 then "Show"

                                    elseif [Date Ranges] = "Last 4 Weeks" and datediff('week',[Custom Start Date],TODAY())<=4 then "Show"

                                    elseif [Date Ranges] = "Last 52 Weeks" and datediff('week',[Custom Start Date],TODAY())<= 52 then "Show"

                                    else "Hide"

                                    END

                                     

                                    I do not have any other Date calcs or filters involved.

                                    1 2 Previous Next