1 2 Previous Next 19 Replies Latest reply on Nov 9, 2017 11:01 AM by swaroop.gantela

# Counting Helpdesk ticket stats.......Version 10.1.1

Hi

Im using tableau in a number of ways one is for sales data etc but another is for IT Helpdesk stats. The problem is i need to have a comparison of how many tickets were opened per month vs how many tickets were closed in that month. I know of the dual axis but so far im unable to get the query to populate this data. I need it to count the amount of Tickets created per month on one line and on the dual axis compare against the amount of tickets with Closed status on another for a KPI. Can anyone help?

• ###### 1. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Can you provide some sample data?

• ###### 2. Re: Counting Helpdesk ticket stats.......Version 10.1.1

HI Wayne,

Find my approach as reference below and stored in attached workbook version 10.1

• ###### 3. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Sure please find attach some sample data

• ###### 4. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Hi this counts the status not the instances a ticket is created per month so i would need to have count of instance of Id in date/month and then second axis amount of status = "Closed" in that same month

• ###### 5. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Hey Wayne,

Have you tried creating two calculated fields?

Calculated field 1:

Open tickets

IF Status = "Open"

THEN tickets

END

Closed tickets

IF Status = "Closed"

THEN tickets

END

Now put these two calculated fields in your view and aggregate them on CNTD so get a distinct count.

Now dual axis them and you should be done.

Cheers,

Matthias

• ###### 6. Re: Counting Helpdesk ticket stats.......Version 10.1.1

i understand but would that calculate the amount of tickets opened not open in that month so basically im looking for a count of the amount of tickets opened per month. The count of status is easy and combining the axis as dual is fine but how can i count the amount of distinct . In other words in basic format

Tickets opened in January: 56

Tickets closed in January 54

Do i count the unique Ticket id number and total them per month?

• ###### 7. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Wayne - Number of open items over time may well be the most complex common use case! There are a couple different ways to address it, but the best place to start is probably the Open & Close Dates FAQ.

If you're only interested in counting open and close activity, that would be much more straightforward, as long as you have an Open Date and a Close Date (ideally each on its own row--the data reshaping techniques in the FAQ can help you get there). It doesn't look like you have a Close Date in your data, though, unless you can safely assume Close Date is IF [Status]="Closed" THEN [Updated].

If that's the case, then you can create two measures ([Open/Close] being a field that indicates whether the row contains an Open Date or a Close Date--remember, each on its own row):

Tickets Opened: IF [Open/Close] = "Date Opened" THEN 1 END

Tickets Closed:  IF [Open/Close] = "Date Closed" THEN 1 END

Then, all you have to do is plot both measures against your [Open/Close Date] field--no need for a dual axis!

• ###### 8. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Hi

Please see below i simply want to know how many times in a month period was a ticket created ie count of ticket ID's or date instances in the one month. Its a very basic excel calculation. Then i want to cross that with amount of closed status in that same month so the report might look like below:

• ###### 9. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Hi Kelly,

Based upon your Sample data for December,16. The view is like this. The dates can be converted into Months or Years if you have complete data set. Since you already have Status field so you can easily divide into open or closed status.

• ###### 10. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Hi

I do not need to count status the status. I need to count the instances that a ticket was created in a each month as in there were 60 tickets created in December. Then on the same chart count the values of the closed status for the same period hence giving me the KPI for amount of closed tickets vs the amount opened (not open) per month. The problem is when i count say the measure for the unique ID no for each ticket created i get my "Tickets opened" value but when i add a count for tickets closed per month the status changes everything to count for only that filter and there is not a possibility for a dual axis which i require.

Thanks.

• ###### 11. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Wayne - A dual axis isn't strictly necessary for your use case; is there a reason why you need to use that instead of Measure Names/Measure Values? See the attached for a  more complete example, using some test data.

• ###### 12. Re: Counting Helpdesk ticket stats.......Version 10.1.1

I too am trying to find a solution to this in Tableau as well.

I have records with both Opened_at and Closed_at date/time fields in them.

I need to see side by side comparison of those records Opened_at in January against those records Closed_at in January.

Just to make it complicated, I have 3 types of tickets in my data sheet that I need to stack in that bar chart.

Any assistance would be appreciated.

Thanks, Lisa

example:

• ###### 13. Re: Counting Helpdesk ticket stats.......Version 10.1.1

How do you do that? I need to produce a chart like that one within a workbook where I have detail data like this:

And I can create a chart if I manipulate the data outside Tableau. But I need to create it within Tableau itself so I can combine this chart with other views about the same items:

• ###### 14. Re: Counting Helpdesk ticket stats.......Version 10.1.1

Lisa,