Skip to main content
Topics#Tableau Desktop & Web AuthoringChristian Gastardo's Question
Christian Gastardo asked in #Tableau Desktop & Web Authoring
Apr 12, 2023, 6:38 AM

COUNTD IDs based on changes in another column

I have a table that contains ticket IDs, status, the date when the data is extracted and a column that indicates if the data is latest or historical.

 

Now, I wanted to know the count of distinct IDs whose status on the latest date is "Open" and was on "Closed" Status at any point before the latest date which means any of the historical rows.

 

The reason I'm getting this distinct count is because these are reopened tickets. Hopefully someone can help me. Been stuck on this today.

 

Here is a sample snapshot of the data below. I'll attach also a csv file for this sample data.

 

COUNTD IDs based on changes in another column 

sample data.csv
3 answers
  1. Saidjon Tojiboev (President's Administrative Office in Uzbekistan)
    Apr 12, 2023, 7:24 AM

    @Christian Gastardo​ 

    To get this, first, create a calculated field to identify the latest date in your dataset:

    {FIXED : MAX(IF [Data] = "latest" THEN [Date Extracted] END)}

    Then, create a calculated field to identify the IDs that were "Closed" at any point before the latest date:

    Closed Before Latest:

    {FIXED [Reference]: MAX(IF [Data] = "historical" AND [Date Extracted] < [Latest Date] AND [Status] = "Closed" THEN 1 ELSE 0 END)}

    Then create a calculated field to identify the IDs that are currently "Open" on the latest date:

    Open on Latest:

    {FIXED [Reference]: MAX(IF [Data] = "latest" AND [Status] = "Open" THEN 1 ELSE 0 END)}

    Last, count the distinct IDs that meet your criteria:

    { FIXED [Reference]: SUM(IF [Closed Before Latest] = 1 AND [Open on Latest] = 1 THEN 1 ELSE 0 END) }

    That's it.

    Let me know if it works

     

    Thanks,

    Said

    (Please upvote all my answers and select them as the best if you found them helpful)

Write an answer...
0/9000