2 Replies Latest reply on Dec 10, 2013 11:02 AM by jenny.nedrocik

    group integer field using calculated field or parameter

    jenny.nedrocik

      I have a field that is being recognized by Tableau as a string field, but it is a date-year [year built] and is formatted like YYYY.  So, I created a calculated field [year built date] to turn the string into a date:

       

      DATEADD('year', INT([year built])-1900, #1900-01-01#)

       

      My next step was to create a calculated field [years since built] to determine the amount of years between the calculated field and this year.

       

      avg(DATEDIFF('year',[year built date],DATETRUNC('year',NOW())))

       

      The last calculated field [years group] was created to group the amount of years.

       

      IF ([years since built]>=1 and [years since built]<=20)

      Then "1-20 years"

      elseIF ([years since built]>=21 and [years since built]<=40)

      Then "20-40 years"

      elseIF ([years since built]>=41 and [years since built]<=50)

      Then "40-50 years"

      elseIF ([years since built]>=51 and [years since built]<=60)

      Then "50-60 years"

      elseIF ([years since built]>=61 and [years since built]<=70)

      Then "60-70 years"

      elseIF ([years since built]>=71 and [years since built]<=80)

      Then "70-80 years"

      elseIF ([years since built]>=81 and [years since built]<=90)

      Then "80-90 years"

      elseIF ([years since built]>=91 and [years since built]<=100)

      Then "90-100 years"

      elseIF ([years since built]>=101 and [years since built]<=110)

      Then "100-110 years"

      elseIF ([years since built]>=111 and [years since built]<=120)

      Then "110-120 years"

      elseIF ([years since built]>=121 and [years since built]<=130)

      Then "120-130 years"

      elseIF ([years since built]>=131 and [years since built]<=140)

      then "130-140 years"

      else ">140 years"

      end

       

      The [year built] and [years since built] calculations, when placed on rows or columns, appear to be accurately giving me the data that I want.  When I place [years group] on columns or rows it only returns the group '40-50 years', but I know that not all of them should be in that group.  When I place the [year built] date on rows with the [years group] then the years show up in the correct groups.

       

      years.png

       

      Also, the [years group] calculated field is only being recognized as a measure and I can't change the data type.  I also tried using a parameter 'range' with a step size of 10 coupled with a calculated field that I put on the filters shelf, but that did not seem to work either.  I don't think my calculations are correct. 

       

      I need a calculated field, or parameter, that can provide users the ability to filter by the groupings of years.  I can't share my workbook so I'm hoping that someone has a solution just by looking at the calculations. 

        • 1. Re: group integer field using calculated field or parameter
          bill.cunningham

          Hey Jenny,

           

          Very hard to troubleshoot this without seeing the workbook, but one guess is that since your formula for [years since built] is using the AVG function it means it is aggregating all of the records as an average which is apparently falling into the 40-50 year range:

           

          avg(DATEDIFF('year',[year built date],DATETRUNC('year',NOW())))

           

          It is displaying correctly when the other formulas are placed on the rows/columns because they are partitioning down to a single record. 

           

          Is there any reason you couldn't handle the date as an integer without converting it to a date?  You could still find the current year using DATEPART function to return an integer and just do your subtraction from there. 

          • 2. Re: group integer field using calculated field or parameter
            jenny.nedrocik

            Hi Bill,

             

            Your idea of using the current year using datepart and subtract from there worked.  I guess I was over-complicating it. 

             

            Thank you very much for your help!

             

            Jenny