Count of Open and Closed date (Two dates in Data set) are showing wrong when applying filter for specific year or month (Tableau)

Question

Raw data before visualization Visualization look like this I am facing one problem in when building a Visualization of IT- tickets which has open and closed date column I have to count each month open & closed tickets everything looks fine until we create Date filter Date filter pic (MMMM YYYYY) from open date column.

Suppose ticket-1 was open on 21-12-2022 and the ticket is closed on (15-02-2023) and we applied filter for to show only current year(2023 include JAN & FEB ) but it gives wrong output of closed tickets of 2023 because year filter(2023) remove all open date rows which not comes under 2023 which also contains closed date which directly result in wrong output of count of closed date until I select previous year month from the filter.

Answer 1

You want to reshape your data into a table with one Date column and a new Action column that has the value Open or Close. So each closed ticket in your original data turns into two rows in the reshaped table, one row for the open event, and a second row for the close event. If the ticket was still open, it should get one row in the reshaped data for the open event.

You can reshape the data with Tableau or Tableau Prep by unioning the table with itself and then using calculated fields and filters to create a single date column, filter out the second row for still-open ticks and to distinguish Open Actions from Close Actions.