4 Replies Latest reply on Mar 21, 2016 10:10 AM by Michael Lattis

    Reassign values based on relative dates

    Michael Lattis

      Hello, I have been struggling with the following problem:

       

      I have data about customers opening and closing accounts as follows:

       

      Customer IDActionDate
      123Open1/1/2015
      123Close2/1/2015
      123Open12/1/2015
      456Open1/1/2015
      456Close2/1/2015
      456Open3/1/2015

       

      My goal is to reclassify certain "Open" and "Close" actions based on the amount of time elapsed between.  Supposing the threshold is 6 months, I would like to use a calculated field to reclassify the action for Customer #456 on 2/1/2015 to "Pause" and the action on 3/1/2015 to "Resume", as follows:

       

      Customer IDActionDateCalculated Field
      123Open1/1/2015Open
      123Close2/1/2015Close
      123Open12/1/2015Open
      456Open1/1/2015Open
      456Close2/1/2015Pause
      456Open3/1/2015Resume

       

      The concept is that for our business purposes, a closure and subsequent re-opening in less than 6 months has a different meaning to us than a closure and subsequent re-opening after more than 6 months.

       

      Please note, "Pause" and "Resume" are complementary actions, and a "Close" or "Open" event can only be reclassified if a reciprocal event occurs with a DATEDIFF of 6 months or less.  Therefore, if for example an account is "closed" today, and is destined to be reopened next month, it must be considered "Closed" (not "Paused") unless and until that future event takes place.

       

      I have limited experience with Tableau and I have attempted to solve my own problem using various approaches with LOD expressions and table calculations, but I don't see the answer yet.  Intuitively I'm trying to do something that apparently can't be done with LOD, and that is to constrain the calculation to each respective [Customer ID] and then determine if there are any "Close" events that precede a subsequent "Open" event by <= 6 months, and if so, reclassify both actions.

       

      I would greatly appreciate any suggestions from the community.

       

      Thanks!

        • 1. Re: Reassign values based on relative dates
          pooja.gandhi

          Hi Michael,

           

          Which version of Tableau are you using?

          • 2. Re: Reassign values based on relative dates
            Michael Lattis

            Hi Pooja.  I'm using v 9.2.4.

             

            Thanks

            • 3. Re: Reassign values based on relative dates
              pooja.gandhi

              Great, see attached. I created 3 calculations. You can use LOOKUP() to achieve this, although I don't understand this part of your question:

               

              "Please note, "Pause" and "Resume" are complementary actions, and a "Close" or "Open" event can only be reclassified if a reciprocal event occurs with a DATEDIFF of 6 months or less.  Therefore, if for example an account is "closed" today, and is destined to be reopened next month, it must be considered "Closed" (not "Paused") unless and until that future event takes place"

               

              If the account is closed today and reopened next month, wouldn't it suffice the condition of being resumed because it opened in less than 6 months from today?

               

              Hopefully, you can alter my calculations based on your need and let me know if you do not understand parts of this solution.

               

               

              In the LOOKUP() function an offset of 1 means next row and an offset of -1 means previous row.

               

              Pooja.

              • 4. Re: Reassign values based on relative dates
                Michael Lattis

                Hello Pooja, and thank you.

                 

                This seems to work as long as the view is set up the way you have it in the example.  However, I should have mentioned in my original post that I actually have a different view of the data in mind as my final project.  I created a 2nd version of your workbook (attached) with "Sheet 2" showing my intended visualization for these data.  In order to create this example, I added another data source which includes a static column of data called "Reclassified Action".  I cannot figure out how to translate the table calculations in your original example so that they can be used to show the data the way I have it in the second worksheet.

                 

                Sheet 2.png

                 

                Perhaps this is because I'm not clear on how to set up table calculations in bar charts or other chart types where it's less intuitive to "see" what they're doing.

                 

                Thank you again for your help,

                 

                - Michael