3 Replies Latest reply on Nov 30, 2018 10:40 AM by Daniel Lawrence

    Date comparison vs window with one date Value

    Daniel Lawrence

      Hello all,

       

        This has been vexing me today.  I have one date column for all entries.  I want to create bins/booleans/sets where this date is compared to a window for customer retention.

       

      What I want to accomplish:

       

      If there is one value out of all values that is true for this:

      ([Date] > DATEADD('day', -60,  CALENDER DATE )) AND ([Date] <= DATEADD('day', -30, CALENDAR DATE

      Then include the Customer ID dimension.

       

      Subsequently, do the same process for the following boolean.

      ([(Date]> DATEADD('day', -30, CALENDER DATE)) AND ([Date] <= CALENDER DATE)

       

      Then take the intersection of two sets.

      Its a pretty basic data analysis.  In SQL it would just a subquery and in python it would just be running an iterative process for each day to see if both booleans are ever correct.

       

      I cannot seem to get it to work in Tableau because the CALENDER DATE is not available to compare to [Date].  I always end up getting [Date] compared to itself even if I make a copy because it analyses each record.

       

      I can get it to work if CALENDER DATE is a static date but not for all values of CALENDER DATE.

      Does anyone have insight into this type of problem?

        • 1. Re: Date comparison vs window with one date Value
          Tushar  More

          Hi Daniel,

           

          I'd use today() function in tableau.

           

           

          Or, You can create a separate date table that you can use as a calendar table. Join this with your existing DS and use it in the calculation.

           

          ~Tushar

          • 2. Re: Date comparison vs window with one date Value
            Daniel Lawrence

            Tushar,

             

            Thank you for your response.

             

            I began by constructing the basic setup with Today().

             

            So it looked like this:

             

            ([Date] > DATEADD('day', -60,  Today() )) AND ([Date] <= DATEADD('day', -30, Today())

             

            ([(Date]> DATEADD('day', -30, Today())) AND ([Date] <= Today())

             

            This was correct for one value, Today.  I want to have a graph over time where the date being compared to is all the dates in the time dimension used.

             

            I also created a separate date table in Excel and used that as a separate data source but because my original data is JSON, I cannot do a cross-database join with Excel and thus cannot join the calendar date table to the JSON data source [Date] field.

             

            I am currently using data blending with the calendar date table with the relationship being [Calendar date] ~ JSON data source [Date] which is leading me to the same problem as before.

            • 3. Re: Date comparison vs window with one date Value
              Daniel Lawrence

              Small success, I think.

               

              I turned my Excel Data into JSON data and I am using that as a secondary data source in my data blend.  I believe I am heading in the correct direction but I am having trouble setting up the formulas in the correct fashion.  From what I understand from https://onlinehelp.tableau.com/current/pro/desktop/en-us/multiple_connections.htm

              the data blend should act as a Left Join from the primary data source, which is what I want I think.

               

              I want the calendar date to be an objective data source that is used as the date dimension.  Then for each date in the calendar date dimension it checks all of the visits data to see if there is a true statement for:

               

              IF ([Date] > DATEADD('day', -60,  CALENDER DATE )) AND ([Date] <= DATEADD('day', -30, CALENDAR DATE)

              THEN [Id (Customer)]

              END

               

              Subsequently:

              IF ([(Date]> DATEADD('day', -30, CALENDER DATE)) AND ([Date] <= CALENDER DATE)

              THEN [Id (Customer)]

              END

               

              I have attached the packaged workbook.

               

              Ultimate Goal:

               

              I want to find Retained Customer Rate over time.

              Customers who had at least one visit between 60 and 30 days previous to the date in question are listed as ACTIVE.

              Customers who had at least one visit between 60 and 30 days previous to the date in question and then had at least one visit between 30 days previous and the date in question are listed as RETAINED.

              Retained Customer Rates = DistinctCount(RETAINED)/DistinctCount(ACTIVE)