2 Replies Latest reply on Oct 31, 2012 10:15 AM by david.severski

    Finding entries that were not present in a previous period?

    david.severski

      I have a data set in SQL Server 2008 that has the following (simplified) structure: "CVE-ID", "IP Address", "Host Name", "Date". The combination of "CVE-ID", "IP Address" and "Host Name" is unique within a specific date. I'm trying to find a way to report via Tableau the number of entries that have changed between Date A and Date B. The specific numbers I'm looking to report on an plot include:

       

      New Entries - Those combinations that did not exist at Date A but do at Date B.

      Removed Entries - Those combinations that did exist at Date A but do not at Date B.

      Unchanged Entries - Those combinations that exist at both Date A and Date B.

       

      I can probably code up some reporting straight in SQL Server to get these numbers, but it seems like there should be some way within Tableau to do this as well. Is there a combination of window or index calculations that will easily get these three summary stats?

       

      David

        • 1. Re: Finding entries that were not present in a previous period?
          Tracy Rodgers

          Hi David,

           

          You should be able to do this using table calculations as you suggested. It depends how the view is set up and how they are being computed. However, using a combination of index and window_sum will allow you to get the desired results.

           

          A calculation similar to the following may help:

           

          if index()=1 and window_count(max([Order Date]))=1 then 'March'

          elseif index()=2 and window_count(max([Order Date]))=1 then 'December'

          elseif window_count(max([Order Date]))>=2 then 'Both'

          end

           

          Hope it does!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Finding entries that were not present in a previous period?
            david.severski

            Hi, Tracy. That's very helpful. I'm _almost_ there, but not quite. I'm still getting comfortable with the partitioning concepts in Tableau.

             

            Attached is a spreadsheet of mock up data. The Result column is what I'm trying to generate via the calculation. Essentially, I want to know if a given ticket is still open, if it was closed between the two periods, or whether the ticket is a new one when using any two dates as dimensions. My goal is to get the "Result" field which I can then count/aggregate for the entire dataset, or for arbitrary levels of detail ("hostname" in this sample data). I'd like to not have to include the prior period at all in the visualization, but I think that may be asking a bit much.

             

            David