7 Replies Latest reply on Jun 13, 2015 6:17 AM by Tom W

    Date / Time stamp data dump every cut off

    Cristina Cruz

      Hi,

       

      How do I create a data dump that collects say total number every cut off daily? Because I am dealing with aging tickets, i can only record it as a snapshot every cutoff time. But not sure how to do that since it's currently not part of the data source.

       

      Any help will be greatly appreciated.

       

      Thanks,

      Tina

        • 1. Re: Date / Time stamp data dump every cut off
          Tom W

          I'd love to help, but you really haven't provided enough details.

          Please start with some sample data.

          • 2. Re: Date / Time stamp data dump every cut off
            Cristina Cruz

            Hi Tom,

             

            Appreciate you reaching out to help me.

             

            So, here's my situation...

             

            Every week, when I update the dashboard I created, I manually take a snapshot of the total shown in here.

            Because this are dynamic tickets, at any point in time, the Grand Total will change.

             

             

            The data above is being pulled using an SQL query and when I publish this on a tableau server, i will set it to refreshes daily. But my issue is for this graph below.

             

             

            So, you see, that is a snapshot that I manually enter in a spreadsheet. But what I'd like to do is to create some sort of a calculation that archives my Total every day like this:

              

              

            DateTotal
            4-May-152,345
            11-May-152,377
            18-May-152,386
            25-May-152,403
            1-Jun-152,413
            8-Jun2,398

             

            Right now, the database does not archive historical data. that's why I'd like a way to create a calculation or a sql to auto archive today's Grand Total, that way, the line chart above will refresh on it's own.

             

            I am very new to tableau and sql. So, I am reaching out to anyone who could help me find a solution to this.

            Would gladly appreciate any help.

             

            Thanks,

            Tina

            • 3. Re: Date / Time stamp data dump every cut off
              Tom W

              When you say the data isn't archived in the database, you mean it effectively gets deleted every day? Or do you mean that the number might change as records are added / deleted?

               

              Tableau won't be able to automatically archive for you, but you could potentially manage this yourself by looking into using an Extract and appending data from your extract. Take a read and let me know if this might work.Adding Data to Extracts

              • 4. Re: Date / Time stamp data dump every cut off
                Cristina Cruz

                The data doesnt get deleted but the status changes, say, this particular dashboard only caters for opened tickets, so, i am only filtering all tickets with problem status<> closed. So, at any one point in time, if one ticket get's closed, then it will automatically be dropped from my filter. So, it is still in the database but the status has changed.

                 

                So, data extracts, i utilize that for refreshing the entire data. But i am not sure, how to graph a dynamically changing total daily?

                • 5. Re: Date / Time stamp data dump every cut off
                  Tom W

                  You could either append data to your extract so your extract contains open tickets for every single day you run the refresh, but that seems like an unnecessary amount of data to hold on to.

                   

                  Personally I would look to see if I could write the calculation for the graph differently to include closed tickets so you can do a point in time calculation.

                   

                  Tableau won't be able to save off versions of data for you.

                  • 6. Re: Date / Time stamp data dump every cut off
                    Cristina Cruz

                    Hi,

                     

                    Yes, i think the better option is the 2nd one. In my data source, I have OPEN TIME, UPDATE TIME, CLOSE TIME. How do I create point of reference to calculate increments? but the tricky part is how do I that by excluding last 3 weeks data based on Update time.

                     

                    You see, our definition of dormant is any open ticket with no UPDATE TIME for the past 3 weeks. So, that even complicated it. But I think creating a calculated field for increments is the way to do it. Say yesterday was my starting point.

                     

                    Is that something you're suggesting to do? can you help create a sample calculation?

                     

                    Would appreciate any suggestion or idea?

                     

                    Tina

                    • 7. Re: Date / Time stamp data dump every cut off
                      Tom W

                      It's difficult to do in Tableau. Personally I would prefer to do it in the database.

                      Take a look at some existing threads i.e.

                      FAQ:  Open & Close Dates

                      Utilization rates over the course of a day - a variation of the queue length problem.