1 of 1 people found this helpful
This can be done using parameters and calculated fields.
The idea is to let user select a date via parameter and the custom calculation will figure out if:
- Incident has started before the selected date, AND
- Incident has ended after selected date OR end date is NULL
If true for both 1 and 2, then the incident was open on selected date.
See attached workbook, which uses Superstore sample data. Order Date is start date, Ship Date is end date.
Thank you for the response! This is very helpful. I don't think you can pull the time into the x-axis though, which I would also like to do, for trending purposes (i.e., to see how many tickets are open monthly, year over year, etc). Am I missing something?
Best way to do this is to create a second data source consisting a simple list of dates at a granularity that you want to report on, and blend these with your open and close data using a join of
Open <= Date
Close > Date
Then just use count(Incident) as your rows and Date (the list of dates) as your columns.
Book4.twbx.zip 14.2 K
Thanks! I was thinking along those lines. I was hoping Tableau would be able to produce the extra dates column, but it's no problem to create it ourselves. Thank you!
I am trying to adapt this solution to get a trend on the number of active employees. The table has a name, entry date and leave date. Up to here everything the same. The only problem I have is that for the current employees there is no leave date yet. How can I integrate them into the count?
Ignacio, I haven't answered your specific question, but the attached workbook might get you moving in the right direction.
I've been wanting to explore the whole start-stop calendar thing for a while now -- just haven't taken the time. My clients have campaigns, sub-campaigns, different artwork, different tags, different whatever -- all starting and stopping at different dates throughout the year. Just keeping track of it all can be a chore. And then tying this back to impressions, clicks, costs, etc. can be a real headache.
But ultimately the first step is to create a simple run-calendar, something like this:
[FYI: This does account for your NULLs, or 'current' employees.]
Next we'll need to deal with counts, impressions, clicks, etc.
Hope this helps a little; I'll keep working.
Hi Shawn, thanks for the tip.
Actually we just bought Tableau in the company and all our dashboardsare in excel were this kind of problem is easily solved with a COUNTIFS. Actually the same problem as for the HR counts I have to calculate a trend with the backlogs (all orders open on a given date), or to calculate a trend with the delivery performance.
Ignacio, I think I got the answer to your question worked out. I took the Close statement out of the Tables for Sheet2:
So now neither of the Joins have anything to do with the Close field. Then I created a calculated field that takes into account the cases that have a NULL for the Close field:
IF ([Open]<=[TheDate] AND [Close]>[TheDate]) OR ([Open]<=[TheDate] AND ISNULL([Close] ))
Let me know if this works for you.
Here's a version based on Shawn's that puts the Null evaluation into Custom SQL, thereby reducing the number of rows returned by the query by about 1/4. Depending on your data volume, this might be worthwhile.
Jonathan, thanks for this post. I've been avoiding getting too far into Custom SQL because, well it's whole other language to learn, and hope spring eternal that coding at that sort of level will be completely unnecessary any moment now. But the more I think about what I've learned on these forums in the past year, the more I realize that we will always be coding something to produce what we want. So my New Years resolution is to learn SQL.
Got any suggestions on where to start?
Here's a starter tutorial: http://www.w3schools.com/sql/default.asp
I've also done a starter training for my co-workers here to get the basic concepts down, if you want we can set up a webex sometime and I can go through it with you.
Once you have a basic handle on the syntax, then start searching the Tableau forums for posts that include Custom SQL and begin looking through those. The joins and where clauses are where the heavy lifting is, for learning I suggest you write them out in English, like "get every row in Sheet1, with any rows from Sheet2 that match on Order ID where Sheet1.Category is Furniture" for a query that left-joins Sheet2 to Sheet1 and has a WHERE clause on Sheet1 on Sheet1.Category = Furniture.
I'll definitely take you up on the offer of a WebEx. But to leverage your time, I suspect others will be interested in sitting in on your class, so let me put this thread into the Digest next week and see if it generates any interest. Meanwhile, I'm off to see what I can figure out on my own. Thanks again.
thanks both of you for the tips. Really appreciated. Yes, it looks like it works, but I still have to work out all the cases where this calculation arises. Anyway, I already enrolled the help of someone in the IT department to help with SQL and give me a couple of tips.
Did you manage to solve the problem without using SQL?