Hello everyone,
I am almost embarrassed to ask this but I keep hitting a wall and have run out of ideas. I have a large file with "Tickets". Each unique ticket has a "date opened" and may or may not have a "date closed". I want to create a viz with a timeline in the x-axis showing on any given day/week/month the number of open tickets, the number of closed tickets, and the backlog (i.e., all active tickets).
My data source has several tables and is far more complex than this, but for this particular case, consider that there is one table with all the tickets info (root) and a related table with scaffold dates (every day from 1/1/09 to 12/31/30). The two tables are related through both the "date opened" and the "date closed" fields with the "Scaffold Date" (I cannot switch the two because the actual data source has another table with locations as the root and there is no relation between locations and dates).
This will probably be super easy, but I walked myself into a corner and I cannot get out of it!
Thanks for any help you can provide :)
@Marina Kittel
Creating an scaffold, if your original data has million of rows, may represent a problem. Maybe, you can test this approach:
a. Create a Dummy Sheet/Table in your Excel/database:
b. Create a 1=1 relationship:
c. Create a unique date field:
IF [Status]="Open" THEN [Create Date]
ELSE [Close Date]
END
d. Create a calculated field for the active cases:
RUNNING_SUM(COUNT(IF [Status]="Open" THEN [Ticket ID] END))-
RUNNING_SUM(COUNT(IF [Status]="Closed" THEN [Ticket ID] END))
e. Create a calculated field for the Count:
ZN(LOOKUP(COUNT([TicketData]),0))
f. Finally, you can create your viz:
Tip, Don't forget to show missing values:
If this post resolves the question, would you be so kind to "Select as Best"?. This will help other users find the same answer/resolution and help community keep track of answered questions. Thank you.
Regards,
Diego Martinez
Tableau Visionary and Forums Ambassador
