5 Replies Latest reply on Apr 6, 2018 1:36 PM by Eric Hammond

    Adding new data every week

    Brock Sibert

      Hello all,

       

      I'm hoping this is the right place to post. To give a little background: I am working on developing dashboards to track the number of contracts by type, how long they have been outstanding, and the level of importance. Every week, I get a report that shows the contracts with its updated characteristics if any. I would like to add the weekly report to the data source that I already have for the dashboards, so that I can compare the most recent report to previous reports (in an attempt to track the progress of the outstanding contracts) but I'm hoping to get some insight from you all on the best way to do this.

       

      Just to try to make this as clear as possible. Say my data source I have now looks like this:

      Contract NameImportanceTypeDays Outstanding
      Contract 1HighLegal29
      Contract 2MediumBusiness450
      Contract 3HighLegal110
      Contract 4LowLegal150

       

      It is a week later and I get a new report that looks like this:

      Contract NameImportanceTypeDays Outstanding
      Contract 1HighLegal36
      Contract 2MediumBusiness457
      Contract 4LowLegal117

       

      As you can see, contract 3 was completed so is no longer outstanding. Contact 1, 2, and 4 have not been completed so their days outstanding have increased to reflect the additional week.

       

      The biggest challenge that I can't seem to wrap my mind around is how do I add this new data into the data source so that I can compare the information from the week 2 report to the data that I already have? If I just addend the data to my current source, it will cause me to have duplicates when I do any sort of analysis right? For example, if my current dashboard calculates the total # of contracts that are between 91-180 days outstanding, I will have 2. When I look at the new report and calculate it, I should have 1. I get that if I just replaced the old data with the new report, it would show 1 contract. However, if I do this, I'm not able to create a chart that shows there were 2 contracts last week, but only 1 contract this week (I would essentially like to be able to track the progress via a line chart or something of the nature).

       

      I'm not looking for any specific Tableau calculations or anything like that at this point. I'm just trying to figure out the best way to approach this in terms of data. Any help is very much appreciated!

       

      Thank you!

        • 1. Re: Adding new data every week
          Ivan Young

          Hey Brock,

          You will need to add some sort of date to each file load, then you can compare over time.

          Regards,

          Ivan

          • 2. Re: Adding new data every week
            Brock Sibert

            Hi Ivan,

             

            Thank you so much for your response! That's what I was initially thinking as well. Would I need to do something like add an As of Date (the date the file was generated) column to each file load and then use an If/then statement of some sort to prevent duplicates when analyzing?

            • 3. Re: Adding new data every week
              Jim Dehner

              Hi Brock

               

              Is there a reason you don't have a date (i.e. week date for the data) in the file - you are suggesting that you would like to history somewhere

               

              If so I would have a column for the Date of the actual data (please make it a real date - could be the monday of the week)

               

              with that all you would have to do is append (see wildcard union Union Your Data ) your data each week and use a relative date filter (Quick Start: Relative Date Filters ) to just see the most current week - I use something like this with a client so they see the latest 2 weeks of data every week even though the d/b in tableau has all the history

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Adding new data every week
                Ivan Young

                Hi Brock

                An as of date would work perfectly.  If you add as of date to your view it won't have duplicates. Just filter the max as of date if you want to see the current state of contracts.  Creating and comparing aging buckets over the as of date will also be very doable once you get the date in there.

                 

                Regards,

                Ivan

                • 5. Re: Adding new data every week
                  Eric Hammond

                  Hi Brock,

                   

                  The updated weekly report is likely pulled from a table that includes Contract Name, StartDate, and EndDate.  If the report is coming from the company where you work then you may be able to connect to the source table directly; it is worth asking.  This avoids the need to refresh your data manually each week and it also opens the door to more extensive reporting.  For example, "how long does the average high-priority contract take?"; "what percent of contracts are legal?"; "How many contracts are being closed each week?", etc.