5 Replies Latest reply on Jun 28, 2018 3:35 AM by Katya Parvanova

# How to create independent date field with range from particular date to now?

Hi everyone,

I need to create a dashboard that shows the trend of unsolved tickets over time. If I set a filter with the current status it will show the current status, but I need historical data, I need to count the ticket before the moment it was solved. I think that I need additional data field with start date MIN(created at) and end date - Now. And I need to compare the solved date with every date in this period. Any ideas how can I create such a field? Or other way to do this?

• ###### 1. Re: How to create independent date field with range from particular date to now?

Are you wanting a count of currently open tickets by day?

So if you have the following tickets.

Ticket     Created Date     Solved Date

1               2018-06-01     2016-06-05

2               2018-06-03     2016-06-07

You would get the counts for each day of

Date               Count

2018-06-01     1

2018-06-02      1

2018-06-03       2

2018-06-04       2

2016-06-05     1 or 2 depending on how you want to count solved date

2016-06-06     1

2016-06-07     1

• ###### 2. Re: How to create independent date field with range from particular date to now?

I think that this is completely different thing, but anyway how you are going to add every single date, or you will use only the dates from created at?

• ###### 3. Re: How to create independent date field with range from particular date to now?

I think that maybe the logic is to sum all created for month, all solved for month, and the difference in unsolved. But I really don't know how to make this calculations in Tableau.

The picture below shows how I see the solution but in Excel.

• ###### 4. Re: How to create independent date field with range from particular date to now?

Hi Katya,

I think you need to pivot the two date columns i.e create and solve.

Pivot Data from Columns to Rows

Then create few calculated fields.

Let me know if this helps or not.

~Tushar

• ###### 5. Re: How to create independent date field with range from particular date to now?

Hi Tushar,

Thank you very much! This solved my problem perfectly! I have never used Pivot before, it is very useful. Thanks again!