Hello and thanks in advance,
I am relatively new at Tableau but I am learning and enjoying the program immensely.
So far I was able to find answers to all my problems in the existing forum discussions but I have now run into one that seems to be genuinely new and I hope some of you may be able to help me.
I am modelling a process that is equivalent to a help desk in a large business. At any point client can open a help ticket, also at any point any of the open tickets could be resolved/closed.
Data is generated on the event to event basis in the following format:
Event Date, Client ID, Event Type (Start/End).
There is a number of questions I want to be able to answer from this data:
1) How many open tickets organization has any given time ?
This one was relatively easy, I simply do a Total Active Cases = RUNNING_SUM(SUM(Starts - Ends)) over date field - if applied to individual clients this also gives me total number of active tickets every client has
2) How many active clients (clients with open tickets) are there at any given time?
This is stumping me. I can calculate whether any given client is active at any given time (simply by doing 'Active Client =
IF Total Active Cases > 0 THEN 1 ELSE 0' on the above sum) but when I try to sum this over clients it tells me I can not aggregate already aggregated quantities.
3) Average number of open tickets per active client
4) Number of new active clients per quarter
5) Number of new tickets by existing clients per quarter
I have a feeling 3), 4) and 5) will be easy if I can figure out 2).
What I would do in excel - and this would be very slow considering a large dataset - is create a new column labeled 'Client event type' with possible values CS, S, E, and CE where CS is assigned if the event is a start of a ticket for a client that has no open tickets on the day of the event and CE is assigned if the event closes last ticket that the client has. Otherwise, value of this column is the same as value of 'event type' column.
I would determine whether or not those conditions obtain by doing a SUMIFS on 'Starts - Ends' with conditions being matching index client ID and date preceding index date. I would then use CS and CE events to compute the number of active clients at any given time.
My question is - is it possible to do something equivalent in Tableau ?
I am attaching a sample tableau file where I started working on the problem (real data has well over a million events) as well as a sample excel sheet where I created the computed Client Event Type column.
I know this one is tricky. Many thanks in advance if anyone has an idea how to proceed.