4 Replies Latest reply on Jul 27, 2018 6:03 AM by Matthew Cseter

    Creating a join on another join

    Matthew Cseter

      Hello,

       

      I have one set of data ("Backlog") and within it I need to adjust the dates based on which dealer we are working with ("Dealers"), and then use the outcome of that calculation to link to our fiscal calendar ("Dates"). I know how to create an 'adjusted date' calculated field which joins Backlog to Dealers using the DateAdd function (so all sales to Steve & Co and Tom & Co we now add 40 and 60 days respectively). Well based on that new calculated field date, I would like to bring in my fiscal period and year from the "Dates" spreadsheet. Unfortunately the calculated field I created does not show up as a join option, and when I use "Create Calculated Field" as the join option I end up with an error. Appreciate the help!

       

        • 1. Re: Creating a join on another join
          eva_lesny

          I tried to replicate how you described your problem.

          For me, the error says:

           

          Dateadd will make a datetime out of your date, wrapping the complete dateadd part in date() can fix that.

           

          You could alternatively just use "[Dates] + [Days]" as your calculation which will give the same results.

           

          BUT: since some of your dealers have NULL as Days you definitely need to wrap Days in a Ifnull() function.

          This will use Days if it is not null and 0 otherwise (ifnull(Days, 0))

          • 2. Re: Creating a join on another join
            Matthew Cseter

            Eva,

             

            That is correct, I can successfully create the formula I need using the "ifnull" and "dateadd" function, and am able to successfully adjust my dates based on the dealer I am shipping to. However, to then use that "Adjusted Date" field in order to bring in my fiscal dates is where I continue to run into issues.

             

            Thanks,

             

            Matt

            • 3. Re: Creating a join on another join
              eva_lesny

              Apologies for the late reply.

               

              I think I should have made it clearer in my other post - when you use the dateadd function Tableau will make a date+time format.

              Your dates in the table are of the format date though.

              Hence there is a mismatch of formats and tableau is unable to join on these fields. The same happens when you try to join a string with a number.

               

              If you just put a date() around the formula you already have in your join calculation it will join correctly

              • 4. Re: Creating a join on another join
                Matthew Cseter

                Thank you so much! Sorry I'm a little slow! Excellent,


                Matt