3 Replies Latest reply on Sep 19, 2016 2:02 PM by Ivan Young

# Challenge with dates and calculated field

Hello all,

I work with a data set of tickets and I am stumped.  The background:

• Each ticket has a "date created" and a "date closed" field.
• The date created field is populated when the ticket is created.
• The date closed field is not populated until the ticket is closed.
• Each of the tickets can be open for several months.

I need to report the number and average days open for the tickets at the end of each month, for the previous 12 months.  My approach thus far is to create a calculated field that determines the days open for a ticket.  But I end up creating a separate calculated field for each month. As an example,  a ticket was opened 15 January and was closed on 15 March.  This results in the following:

• January days open: if date closed is null or > 31 January then days open = end of month date (31 January) - date created (15 January)= 16 days open
• February days open: if date closed is null or > 28 February then days open = end of month date (28 February) - date created (15 January) = 44 days open
• March days open: if date closed is null or > 31 March then days open = end of month date (31 March) - date created = null (null value because ticket was closed on 15 March)
• THis ticket would be counted as open in January and February but not in March.

Next, I build a graph to show the total number of tickets at the end of each month and a second graph with the average days open for open tickets by month.  This is where I am stumped.  in the example above, I have three separate calculated fields.  However, I need to plot the three days open values (January: 16, February: 44, March: null) in a "days open" graph and a "number of tickets open" graph.

Any ideas or suggestions?

thanks!

the tickets

• ###### 1. Re: Challenge with dates and calculated field

Hello Robert - First of all I have almost no idea related to the process of Ticket Generation system and how management looks at the data. Apology for that. I have made an assumption for ease of calculation that if Close Date (Ticket is open) is Null then that is considered as Today.

Now the data looks like following :

Now I have created a Calculated field called "Logical Close Date"

and then Calculate Open Days

Find out the the Number of SR raised per Month called "Count of SR"

Finally the Viz. looks like this:

Will this help ?

Thanks,

Manideep

• ###### 2. Re: Challenge with dates and calculated field

Hi Manideep,

Thanks for the reply.  It's close but not quite.  I modified your excel file to show you the desired outcome. See below and the attachment.

As you see, in Excel, this is relatively straightforward, but I can't figure it out in Tableau.

I appreciate the help!

Rob

• ###### 3. Re: Challenge with dates and calculated field

Hi Robert,

Tableau generally doesn't do well reporting with data that doesn't exist.  In your case the month end dates do not exists in your dataset.  Creating a  table of month end dates then full joining on your dataset would make it bit easier to generate your desired output.

I've attached a twbx demonstrating this process to achieve your desired outcome.  Let me know if you have any questions.

Regards,
Ivan