I'd love to help, but you really haven't provided enough details.
Please start with some sample data.
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:
Date Total 4-May-15 2,345 11-May-15 2,377 18-May-15 2,386 25-May-15 2,403 1-Jun-15 2,413 8-Jun 2,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.
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
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?
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.
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?
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.