5 Replies Latest reply on May 3, 2018 6:58 AM by Kelly McGrady

    Year attribute as a calculated field

    Kelly McGrady

      Okay so I have a bunch of separated out columns like Mar FY2016 Unallocated I need this to be assigned 2016 as a year if it actually ends up as a date type is irrelevant at this point.

      I have all of the months from 2015-2018 and I need to be able to make those 4 years into buckets pretty much to make call outs.

      Can anyone help the standard if then format didnt want to work.

       

      Kelly

        • 1. Re: Year attribute as a calculated field
          Joe Oppelt

          Are you saying the value in the field is:  "Mar FY2016 Unallocated" ?

           

          Will the year portion always fall in the 7th character?  If so, use the MID function to grab from 7 through 10, and wrap that in the INT function to make it a number.

           

          But if the position isn't fixed at the 7th character, then use the FIND function to find where the 2 is (assuming that there won't be some other occurrence of a 2 before the year portion of the string.)  Then use the MID function to grab from wherever the FIND found the 2, plus 3 more characters, and wrap that in the INT function.

           

          So the short answer is, "it depends".

           

          But yes, you can do it.

          • 2. Re: Year attribute as a calculated field
            Michael Ye

            Kelly,

             

            Provided the dimension name for the "Mar FY2016" is "Date".

             

            Make a calculated field: Year:

             

            RIGHT(Date,4)

             

            However, this is a string. If you need a date format, use:

             

            MAKEDATE(INT(RIGHT(Date,4)),1,1)

             

            This is a date format, and it makes the date 1/1/2016.

             

            When you drag it to view, use Year() it will five only the year 2016.

             

            Hope it helps.

             

            Michael Ye

            • 3. Re: Year attribute as a calculated field
              Kelly McGrady

              I think I should have been more clear in what i need

              I have a whole bunch of columns that are NAMED [Mon FYYear Unallocated]

              the values in each column are floats and they are right next to each other. I need some way preferrably to make a column with 2015-2018 as the values that will add up those with the corresponding year

               

              so i'll get a view like

              2015                                          2016

              Total Unallocated : $XXX           Total Unallocated : $XXX

               

              ect

              • 4. Re: Year attribute as a calculated field
                Joe Oppelt

                Your data is not "shaped" in a way that you can easily do what you want to do.

                 

                Is this an excel data source?  If so, you can edit your data source in tableau and PIVOT those columns so that they all become one column, with a separate row for each value under each original column name.  You'll get two columns from this.  One will be called [Pivot Field Names]. with values that were the original column names.  The other will be [Pivot field values] with the FLOAT value you had in the original columns.  Then you can do the sorts of manipulations we talked about earlier.


                If you're not using an excel data source, then you will probably have to ETL your data (pre-process your data) outside Tableau before creating a data source with it.  If you're using SQL to generate your data, SQL has a pivot function, for example.

                1 of 1 people found this helpful
                • 5. Re: Year attribute as a calculated field
                  Kelly McGrady

                  Thank you I appreciate the help. I was worried that this would be the case but I had to try anyway thank you.