2 Replies Latest reply on Dec 15, 2016 4:52 AM by Paula Sims

# How to calculate point in time totals and then display

We have raw data that shows IT ticket incidents by incident type, start and end. What I need to is to calculate is best shown as follows:

Incident Type

# of tickets open as of Today's Date

(ie 12/14/2016)

# of tickets open as of Most Recent Qtr End

(ie 9/30/2016)

# of tickets open as of Most Recent Quarter End - 1 Quarter

(6/30/2016)

# of tickets open as of Most Recent Quarter End - 2 Quarter

(3/31/2016)

# of tickets open as of Most Recent Quarter End - 2 Quarter

(12/31/2015)

# of tickets open as of Most Recent Quarter End - 2 Quarter

(9/30/2016)

IT Hacking53810151
User Installs15156103
TOTAL IT TICKETS OPEN40192321304

My raw data in the SQL table looks like this

Incident TypeStart DateEnd DatePerson
IT Hacking1/1/201612/31/9999Jane Doe
IT Hacking6/3/201612/10/2016Happy Gilmore

I do get the result above in Excel but it's tedious. I'd like to be able to do it in Tableau. Can I do it with SQL statement(s) with dynamic date changes beginning with the run date (current date) and then the most recent 5 quarter ends back? I'd prefer not to have to feed it into Tableau but rather a direct calculation.

Also, if  you can suggest a graphical representation other  than a bar chart, that would be appreciated. It's not really a gantt chart but it sort of is.

Thanks

• ###### 1. Re: How to calculate point in time totals and then display

Hi Paula,

You need to use the datetrunc() function extensively.

[# of Tickets open today]

SUM(

If [Start Date] = Today THEN 1 ELSE 0 END

)

[# of Tickets opened as of Most recent Quarter End]

// Count all tickets that have Start Date BETWEEN, first and last date of the last quarter

SUM(

If [Start Date] <=DateADD('day',-1,DATETRUNC('quarter', TODAY())) and [Start Date] >=DateADD('month',-3,DATETRUNC('quarter', TODAY())) THEN 1 ELSE 0 END

)

Similarly you can build the formula to find ticket count for Start Date beween first and last day of previous to previous quarter.

Best,

Sujay

• ###### 2. Re: How to calculate point in time totals and then display

Thanks Sujay - really appreciate this. One question, though. . . How do I dynamically name/label the variables? Same manner? As a calculations?

Thanks