3 Replies Latest reply on Nov 27, 2012 9:25 AM by Catherine Rivier

    Dynamic Date based Calculated Field

    Mark Rouse

      In my workbook, I have the following data:

       

      IDTrans_DateStart DateEnd Date
      101/07/200623/06/200615/07/2006
      210/04/200719/03/200722/04/2007

       

      I'm trying to create a dynamic date calculated field.  I have a parameter setup for ID, with two values 1, 2.

       

      In my calculated field (USE_DATE), I was hoping to do something like this:

       

      case [ID]

      when "1" THEN [Trans_Date] >= "23/06/2006" and [Trans_Date] =< "15/07/2006" end

       

      Of course this is not working (Cannot use Boolean type in case expression).

       

      My idea is to add USE_DATE to my columns card to control what date range is displayed on the axis.

       

      I can easily use a Trans_Date filter and manually change my date to whatever, but this is not feasible when I have thousands of IDS, and manually typing in all those Start and End Dates will take too much time.

       

      Surely, there must be away to do this in Tableau.

       

      Regards

        • 1. Re: Dynamic Date based Calculated Field
          Catherine Rivier

          The issue with your first statement is that you're not using the IF statement to set the field to anything.  If you think of the table, if 50% of your rows are ID 1, you're trying to create a new variable where, for those records, it is ">=23/06/2006", which doesn't make sense.

           

          Instead what you want is to have the Trans_Date field show up only if for that ID it falls between the Start Date and End Date.  As you've set it up, a Case statement won't work (it can only use one variable at a time), so switch to an IF/THEN:

           

          IF [ID]=1 AND ([Trans_Date] >= #06/23/2006# AND [Trans_Date] <= #07/15/2006#) THEN [Trans_Date]

          ELSEIF [ID]=2 AND ([Trans_Date] >= #03/19/2007# AND [Trans_Date] <= #04/22/2007#) THEN [Trans_Date]

          END

           

          All other values (e.g. values for those IDs not in between the Start and End dates) will be null, so will not show up in your data.

           

          I don't know your data, so this would likely need tweaking (is ID unique; if not are the dates fixed per ID, etc.) - but if you want it to not a little more fluid, you can replace with variables, something like:

           

          IF [ID]=1 AND ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Trans_Date]

          ELSEIF [ID]=2 AND ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Trans_Date]

          END

           

          You may also be able to remove the ID field - but you'll have to adjust based on your data and how you want it displayed.

           

          Hope this helps!

          1 of 1 people found this helpful
          • 2. Re: Dynamic Date based Calculated Field
            Mark Rouse

            Hi Catherine,

             

            Thanks a lot for that, its real helpful.  I think I understand now.  I've only given two IDs as an example, but actually have 135 different IDs with different Start and End Dates.

             

            Should I just do 134 ELSEIF statements?

             

            How many ELSEIFs can Tableau handle?

            • 3. Re: Dynamic Date based Calculated Field
              Catherine Rivier

              Actually, it should work just fine by taking out the ID restriction - I tested on some sample data.

               

              IF ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Trans_Date]

              END

               

              This will work on a row-by-row basis, so as long as your data is setup like the above, this should work.  By the way, you'll want to create one for any variables you're showing, besides Trans_Date.  For example:

               

              IF ([Trans_Date] >= [Start Date] AND [Trans_Date] <= [End Date]) THEN [Volume]

              END

               

              A lot less work than 134 ELSEIF statements   But for your question on how many Tableau can handle:  I do believe it doesn't have a cap on the number you can have (like with SQL), but you may see performance impact after the first few hundred....