2 Replies Latest reply on Sep 17, 2012 1:57 PM by Jason Cluggish

    Calculated Field queries

    Jason Cluggish

      We are trying to use a calculated field to display a sentence that contains some date information which is pulled from discreet dimension database columns.  The database dimensions used are:


      [begin_year] - Numeric datatype

      [end_year] - Numeric datatype

      [begin_month] - Varchar2 datatype

      [end_month] - Varchar2 datatype


      We would like to have a calculated field like this:


      'Your report runs from '  + [begin_month] + ' ' + [begin_year] + ' through ' + [end_month] + ' '  + [end_year] + '.'


      Which would produce the sentence:


      Your report runs from July 2011 through June 2012.


      With me so far?  The issue is that the sql that Tableau creates for the calculated field is so immense and unwieldy that it times out.  I've been able to create a calculated field that uses just two database dimensions such as this:


      'Your report runs from '  + [begin_year] + ' through ' + [end_year] + '.'

      Which produces this:

      Your report runs from 2011 through 2012.


      Adding more that two database dimensions into the calculated field just kills it.  This is an Oracle database using a single table data source with three filters on it.  The database table is of reasonable size 25K rows.  This is not a cube, just a straight forward db table using Tableau Desktop 6.1.


      Does anyone have any tips on how to get calculated fields to create more manageable sql?  I've tried experimenting with wrapping the database dimensions with the ISNULL function, but that doesn't seem to help. Like this:

      'Your report runs from '  + ISNULL([begin_year], '') + ' through ' + ISNULL([end_year], '') + '.'


      Any help would be greatly appreciated!

        • 1. Re: Calculated Field queries
          Tracy Rodgers

          Hi Jason,


          I'm not sure if this will help, but what if you combined the begin year and begin month fields into a calculation and end month/year into a separate calculation, then use the two calculations in your final calculation?


          Are you able to post the workbook (as a twbx file)?



          1 of 1 people found this helpful
          • 2. Re: Calculated Field queries
            Jason Cluggish

            Hi Tracy,


            I'll try your idea of combining the calculated fields and then using the combined fields in the final calculation.  There is always more than one way to skin a cat.  Although, I'm still wondering if there is anyway to get the "behind the scenes" queries improved. In the situation I described, using four calculated fields creates a select query with 10 nested CASE WHEN statements.