6 Replies Latest reply on May 2, 2017 10:29 AM by Justin Larson

    How to calculate an exact date with period # and start/end dates?

    Tiffany Rapp

      I have time-reporting data on top of which I've built a Tableau workbook. My data source includes a period number, and the start/finish dates of each period. There is a total # of hours reported by person, as well as fields for slice 1 -7 which correspond with weekdays, Saturday through Sunday.

       

      Currently the user has to filter the time period by "Period Finish" dates, but I've been asked to allow people to filter to exact days, or to look at a calendar month at a time (and the period finish dates don't usually correspond to a calendar month). I cannot figure out how to make this happen. I've attached a simplified, de-identified packaged workbook, and I added a dates table that I thought would help, but I'm not sure what to do with it. I need help, please and thank you!

        • 1. Re: How to calculate an exact date with period # and start/end dates?
          Justin Larson

          Dealing with multiple date fields always tends to jack up logic, which is a typical situation to run into with data that represents date ranges.

           

          There is no single answer to this question, so much as a number of different ways to handle it, each with their own disadvantages. The "best" may be a SQL-based solution, because SQL specifically can handle complex relationships between tables that result in various outcomes, including limiting rows, duplicating rows, introducing nulls, etc, which can be exactly what you need. (e.g. join to a calendar table, where join criteria is calendar date is between the two date fields on data table) It is possible to write SQL against an Excel source, when you connect to the file, hit the caret next to "open" and open with legacy connection, then on the Data menu, there is a Convert to SQL command, and you can treat your Excel file like a database, which is pretty cool. If you want to try that and you are not seeing the "Legacy" option, you probably have 64 bit Excel and Tableau, and you just need to run out and install the Microsoft Access driver on your machine, and it will show up. (free: Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center )

           

          If SQL is not your deal, I would understand. Another option is to create a calculated field that evalutes at a row level against a parameter inputs. The row level calculation will simply check if [UserDateInput1] is between [Period Start] and [Period Finish] OR [UserDateInput2] is between [Period Start] and [Period Finish]. At a row level, if either of the dates in question is within the range of the row, the row will evaluate to true. Then you throw that calculation on your filter. It's not precise, because you will pick up Periods that extend beyond your dates, but that is the level of detail within the workbook.

           

          I'll stop there and let others throw in their 2¢

          • 2. Re: How to calculate an exact date with period # and start/end dates?
            Joe Oppelt

            You can pivot those "Slice" columns.


            In the attached I did Edit Data source on your Sheet3 data source.  Then I did ctrl-click on the 7 "Slice" columns, which highlights all 7.  Then right click and select "Pivot".

             

            Now you get two new column names in your data source, and they replace your 7 "Slice" columns.

             

            In the attached I made a sheet (I just selected NICK since he is at the top of "Show Data".)  You can see what it looks like in the sheet.

             

            From there I created a calc that extracts the number value from the "SliceN" value that the pivot creates.  I used this number to do a DATEADD to build the new date values you need.  (See [Make a date] calc.) )

             

            Now you have a complete date range to run on an axis.  Your user can select a date, and you'll just give them [Make a date] to select on.

            1 of 1 people found this helpful
            • 3. Re: How to calculate an exact date with period # and start/end dates?
              Tiffany Rapp

              This is very cool! But will work only with Excel or other text sources, right? I am actually connecting to a SQL server database, but changed it so that I could mask the data. I appreciate the help!

              • 4. Re: How to calculate an exact date with period # and start/end dates?
                Tiffany Rapp

                Thanks Justin. I am using custom SQL for my data source, but I am still new-ish to SQL. I've not been able to figure out how to solve with SQL without causing myself issues in the data source, but I will see what I can do with these suggestions and let you know what I find, thank you!

                • 5. Re: How to calculate an exact date with period # and start/end dates?
                  Joe Oppelt

                  Pivot will work on your SQL data source too.

                  • 6. Re: How to calculate an exact date with period # and start/end dates?
                    Justin Larson

                    I misread your original post in regard to the meaning of those "Slice" columns. I was envisioning the range of Start/Finish on each row to be different.

                     

                    Joe's solution works great, and will work against any source. Ragged dateframes are more complicated, which is what I had in mind.