Each situation can be a little different, so I cannot say for sure without seeing the structure of your data, and the layout on your worksheet, but my first thought is:
You can use Data Blending to resolve the issue. You could create a list of all possible combinations, and use this list as the primary (data source you first place from), then you can handle months with no cases.
If you can share additional details, or even just the field names, and what shelfs they are on in the worksheet, I can be more specific.
You do not need to export data to Excel: you should download the NotesSQL ODBC driver from IBM.
Unfortunately, Tableau can still not connect with this ODBC driver (see my post here: Re: Connectable to Lotus Notes?).
But at least you can connect your spreadsheet to the Lotus Notes application (you have to create a DSN for that)
hi all - apologies for opening an old thread but i am looking to do something very similar. I have followed Joes earlier posts and have the custom SQL working which was great for ones i have that are closed
However i realised that i have multiple columns in my DB for the various closure categories so dont have 1 end date.
So i have in the DB:
TicketNo; Create Date; Solved Date; Cancelled Date; Closed Date
I guess what i only really need to do is consolidate the 'Cancelled Date' or 'Closed Date' into 1 END DATE (there can be only 1 date, or blank if neither closed or cancelled!) but unsure how to write the SQL to do this. Sorry i have extremely limited sql experience.
if anyone can help i would appreciate any advice. Thanks
Hi Joe Mako
I'm struggling to solve a problem that is extremely similar to the one discussed here. I've implemented the Custom SQL connected as suggested which doubles my record count and I replaced your data sources with mine. After cleaning up references to fields that existed in yours but not in my data, I hoped that I could use your OverallActivebyDay view (here: | Tableau Public) to be able to produce a view where I could aggregate to a user-selectable level (weeks or months rather than days or even an arbitrary number of days, then group up the tickets into categories, drill down for further analysis etc.
I've verified that that this view is giving me accurate active tickets on a day by day basis (although strangely one day out); however, when I changed 'Event Date', to 'MONTH([Event Date])', the figures no longer matched my figures tested in a different workbook for the same data source - they were actually about 50% of the figures I got elsewhere.
What do I need to do to change the level of aggregation and maintain accuracy?
Many thanks in advance.
I am happy to help, but in order for me to help, I would need you to provide a sample packaged workbook that represents your situation. Another option is you can email me, and we can meet for a screen sharing session to walk through your situation together.
I probably can't upload a workbook because of its size and the sensitivity of the data. I'll find your email...
Hi Joe Mako
I stumbled upon this post in my search for an answer to a similar issue. Although, mine is a little bit complicated than just counting opened or closed tickets.
Wanted to see if you have some time so, we can discuss my situation. Let me explain it briefly...
So, here's my situation...
I created a dashboard that calculates the total 3 - week dormant tickets. Dormant is defined as OPEN tickets having no activity for the past 3 weeks. To do that, i created a calculated field that excludes all tickets where UPDATE TIME < 3 weeks from now. So, that part is easy. as you can see, the worksheet is shown below. I am using an SQL query and when I publish this on a tableau server, i just set it to refreshes daily and that is fine.
Now, this is where I struggled. I dont know how to create a calculated field that takes a snapshot of daily open dormant tickets that will automatically plot it in similar line graph below. So, you see, that is just 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 counts based on opened, closed and updated tickets. But I am not sure where to start?
in my data source, i have open time, update time, and close time per incident ticket. i believe i needed to have a starting point like an index and add increments but not sure how to do that. Can you help?
Would appreciate any thoughts that you may have.
Sure, I am happy to help, please send me an email and we can setup a screen sharing session to walk through your situation together. Thank you!