Creating table with dimension as rows and days between dates as columns

Question

I have a data set of tickets broken into categories based on their operational category. I also calculated the time in between the start date and resolve date for each of these tickets. I want to display the count of each number of days to resolve per category.

Such as this:

I have tried including my calculated days to resolve variable as a column header but it is not working.

Answer 1

You might find this simpler formula helpful, for this or other cases:

IF DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT]) < 30 THEN
    str(DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT]))
ELSEIF DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT]) >= 30 THEN "30+"
ELSE "Still Open"
END

You might also find it useful to create another calculated field, such as [Time to Resolution] with formula

DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])

It should default to being a Measure but you'll sometimes want to use it as a Dimension, such as in this question. You can drag it to the Dimensions area on the left, or, when it is being used in a visualization, right-click it and choose the Discrete option. (Note that Measure vs. Dimension isn't exactly the same as Continuous vs. Discrete, but usually line up...if things are weird play with both of them until you get what you want!)

This would probably help with analyzing the data in general, and simplify the original formula to

IF [Time to Resolution] < 30 THEN str([Time to Resolution])
ELSEIF [Time to Resolution] >= 30 THEN "30+"
ELSE "Still Open"
END

(Note that the choice of 30+ vs. >30 is just aesthetic. I find that the "+" is more eye-catching and intuitive, especially for business users, it sorts a bit better and lines up left-justified numbers. Obviously the >= needs to change to just > if you go with >.)

Answer 2

So I was able to get it to work making a huge formula for datediff instead of something simple. I am sure there is a more simple solution but here is what I did for my "Time to Close" category:

IF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 0 THEN "0"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 1 THEN "1"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 2 THEN "2"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 3 THEN "3"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 4 THEN "4"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 5 THEN "5"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 6 THEN "6"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 7 THEN "7"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 8 THEN "8"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 9 THEN "9"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 10 THEN "10"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 11 THEN "11"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 12 THEN "12"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 13 THEN "13"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 14 THEN "14"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 15 THEN "15"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 16 THEN "16"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 17 THEN "17"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 18 THEN "18"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 19 THEN "19"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 20 THEN "20"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 21 THEN "21"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 22 THEN "22"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 23 THEN "23"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 24 THEN "24"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 25 THEN "25"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 26 THEN "26"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 27 THEN "27"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 28 THEN "28"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 29 THEN "29"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
= 30 THEN "30"
ELSEIF (DATEDIFF('day',[reported_date_DT],[last_resolved_date_DT])) 
> 30 THEN ">30"
ELSE "Still Open"
END

Making this my columns, my operational category my rows and dragging count of operational category to text gave the desired result.

Answer 3

Create a calculated field to find the difference of dates using Datediff(). Place the calculated field in column shelf and category in row shelf and count(ticket-id) in text shelf to achieve a similar table like yours. Hope it helps!