3 Replies Latest reply on Jul 24, 2018 12:33 PM by Janella Thomas

    How to Output lists of IDs that are in One Field and Not the Other

    Janella Thomas

      My goal is to create a calculated field that outputs a list of the IDs that appear in April but not May and another calculated field for the IDs that appear in May but not April. I also want to be able to count the number of IDs that do not have a match for each month as well.

       

      I believe the issue has to do with the original structure of my data. The data I am using is stacked (where the IDs are repeated based on the month) but I would like to maintain the structure of the data to cut down on additional data prep efforts.

       

      I have created a small data set and attached a workbook. I would appreciate any help!

       

       

      This is what I see in Tableau:

      This is what I want to create calculated fields for:

       

      In April but Not MayIn May but Not April
      812345444567
      912345555567
      112347666667

      April - 3 do not have matching value

      May - 3 do not have matching value

        • 1. Re: How to Output lists of IDs that are in One Field and Not the Other
          Aaron Sheldon

          This one can be accomplish through the combination of a level of detail expression and a cascading conditional test. We start by assuming the data set contains at least the following two fields, whose records maybe degenerate:

           

          1. [ID] the identifier to search for month over.
          2. [DATE] a date representing the time interval the identifier falls in.

           

          To makes this dynamic so that the user can choose which months to compare start by creating two date list parameters,[LEFT] and [RIGHT]. Both parameters are a list of any days you would like. We then create a new field [COMPARE] that uses a level of detail and a cascading conditional to test if the identifier fell in each possible combination of the months:

           

          // Test for records in both months

          IF 0 < { FIXED [ID] : SUM(IIF(DATETRUNC('month', [LEFT]) = DATETRUNC('month', [DATE]), 1, 0)) }

              AND 0 < { FIXED [ID] : SUM(IIF(DATETRUNC('month', [RIGHT]) = DATETRUNC('month', [DATE]), 1, 0)) } THEN

              'both'

           

          // Test for only records in the left month

          ELSEIF 0 < { FIXED [ID] : SUM(IIF(DATETRUNC('month', [LEFT]) = DATETRUNC('month', [DATE]), 1, 0)) } THEN

              'only ' + STR(DATETRUNC('month', [LEFT]))

           

          // Test for only records in the right month

          ELSEIF 0 < { FIXED [ID] : SUM(IIF(DATETRUNC('month', [RIGHT]) = DATETRUNC('month', [DATE]), 1, 0)) } THEN

              'only' + STR(DATETRUNC('month', [RIGHT]))

           

          // Otherwise neither month

          ELSE

              'neither'

          END

           

          To You should then be able cross tabulate with [COMPARE].

          • 2. Re: How to Output lists of IDs that are in One Field and Not the Other
            Jim Dehner

            Hi Janella

            see the attached

             

             

            place the filter on the filter shelf and set to true

             

            it returns this

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: How to Output lists of IDs that are in One Field and Not the Other
              Janella Thomas

              Hi Jim,

               

              This works wonderfully! How would I be able to get a count of the number of records that appear?

              In this case, 3.

               

              Janella