6 Replies Latest reply on Sep 20, 2016 11:24 AM by Susie Barrett

# Creating a plain Date field that will show month/year for rolling average of open tickets

I am trying to show a line graph of the average age of open tickets, month over month.  I have a line graph and a calculated field that shows the average days, but the only date field I have is the Created, or Opened, date, which then only gives me those tickets created during that particular month.

I can't figure out how to the average age of a ticket opened in January of 2015 that is still open, or any other ticket that is still open today.  I tried to use Alteryx, but being a novice in Tableau is bad enough

Any help would be superb

• ###### 1. Re: Creating a plain Date field that will show month/year for rolling average of open tickets

Hello Sally,

Sally Niemi-Farhoody wrote:

but the only date field I have is the Created, or Opened, date, which then only gives me those tickets created during that particular month.

Then what kind of date are your expecting?

According to your worksheet "Avg. Age of Open Problems", for example,

among all the tickets created in Jan 2015, 2 of them are still open now, the average age of them is 517 days.

This makes sense, doesn't it?

Regards

• ###### 2. Re: Creating a plain Date field that will show month/year for rolling average of open tickets

Hi Lei,

Thank you for responding.  I have the created or opened data field, and yes, I can see that as of all the tickets created in January 2015, only 2 are open with the average age of 517 days.  But I'd like to add to that number all tickets in open state in January 2015, not just the ones created.  So anything in open state during Janaury (this includes those created in January) I need the average age.

I don't know how to add the count of those that are in open state to those that are created that month.  The end goal is to have an ongoing line chart showing the number of open tickets each month, and the average age each month.  For example, as of March 31st, there were 48 tickets opened, and a total of 241 tickets in open state.  So I want to show the 241 for March.

Does this explanation help?

• ###### 3. Re: Creating a plain Date field that will show month/year for rolling average of open tickets

Hello Sally,

Yes, now I understand.

There are created date, closed date, but another date, I call it observe date is necessary.

I created a new data source which is the union of created date and closed date.

To make the sample simple, this data source is to the level of month.

By using data blending, I created this data table (screenshot).

For example, at the end of Mar 2016,

- 64 tickets were created in Mar 2016

- 48 of them are still open now (in other words, 16 were closed in Mar-Apr 2016)

- 39 tickets were closed in Mar 2016 (including the ones newly created in this month and historical ones)

- 367 tickets remained open at the end of Mar 2016

Yet I haven't got any 241 for Mar 2016.

There are several dates here, statistics vary at different observe time point.

So could you explain your definition for 241 and where it comes from?

As for the age, I'd like to confirm how you define it.

For example, 367 tickets remained open at the end of Mar 2016.

- Does age stand for the DATEDIFF from ticket created date to today?

- Does age stand for the DATEDIFF from ticket created date to Mar 2016?

Please refer to the attached workbook (modified via 9.3) for details.

Regards.

• ###### 4. Re: Creating a plain Date field that will show month/year for rolling average of open tickets

Hello Lei,

I can't open the workbook as my version is only 9.2.8 - we can't upgrade because of our server version.  I will try to add a newer version on another machine.  To answer your questions:

Age stands for the datediff from (today) to created date.  I was trying to create a parameter so I could do a 'point in time', but for this exercise (today) is fine.

I got 241 from looking at all open tickets for the month of March.  I had been trying to use Alteryx  to determine ticket backlog, but will need to do some self paced training.

Thanks -

Sally

• ###### 5. Re: Creating a plain Date field that will show month/year for rolling average of open tickets

Hello Sally,

Sally Niemi-Farhoody wrote:

Hello Lei,

I can't open the workbook as my version is only 9.2.8 - we can't upgrade because of our server version.  I will try to add a newer version on another machine.  To answer your questions:

Age stands for the datediff from (today) to created date.  I was trying to create a parameter so I could do a 'point in time', but for this exercise (today) is fine.

I got 241 from looking at all open tickets for the month of March.  I had been trying to use Alteryx  to determine ticket backlog, but will need to do some self paced training.

Thanks -

Sally

I add average age column (simply in months) to the previous table.

I'm still feeling confusing for 241.

I got 367, which consists of four parts.

- 81 Closed, in Apr 2016, which was still open in Mar

- 56 Known Error, I treat these part as open

- 229 Open, still open till now

- 1 Pending Change, the close date of which was in Apr 2016, I suppose it was open in Mar

Please let me know if there's any misunderstanding for this.

Updated workbook is attached.

Regards.

• ###### 6. Re: Creating a plain Date field that will show month/year for rolling average of open tickets

What would you do in this situation if you can't add additional columns to the original data?  So, instead of a join, I would assume you would need to use a blend?  If so, how would this change the formulas?  I tried modifying the information for a blend, but get an error re: aggregation.  Thanks! -Susie