2 Replies Latest reply on Feb 6, 2013 6:55 AM by Jason Back

    Creating Date Calculated Field with Mutiple Extracted File Sources

    Jason Back

      Hello,

       

      I want to create a date dimension that has mutiple extracted file sources.

       

      Basically, in excel column my data is such:

       

      Year

      2006

      2007

      ....

      ....

       

      When I connect mutiple files and crated the date using the following calculation it was fine:

      DATEADD('year', ([Year] - 2000), #1 January 2000#)

       

      But then when I extract data to reduce the file size the calculation does not work anymore.  The simple example file is attached.

       

      I would very much apprecaite any suggestions.  Thank you very much.

       

      Jason

        • 1. Re: Creating Date Calculated Field with Mutiple Extracted File Sources
          Mark Holtz

          Hi Jason,

           

          As a general observation, Tableau is much more powerful when you feed it dates than integers representing years...

           

          To your question, when using a field from a secondary data source in a calculation, it must be aggregated. (i.e., ATTR for dimensions, SUM, AVG, MAX, MIN for measures...) I suspect that when you invoked an extract, Tableau converted the YEAR value to a continuous (green) measure since it was an integer in your underlying sources. You need to move it (drag) to Dimensions (it will turn blue indicating that it has transformed into a discrete dimension).  Then, you can re-edit each formula. Just delete the [Year] and reinsert by dragging it in from the appropriate source. You will see Tableau wrap the ATTR([Sheet1 (2.xlsx)].[Year]) around it, which means it's being aggregated.

           

          Just out of curiosity, could you just make the year field an actual date in your underlying sources (e.g., 1/1/2006)?

          You would then have a number of options to relate data from source1 to data from source 2 (data blend, use dashboard actions on date, etc

          • 2. Re: Creating Date Calculated Field with Mutiple Extracted File Sources
            Jason Back

            Hi Mark,

             

            Thank you the reply.  Your method, however, puts the date down to the measures, which seems to me that it doesnot function as the exactly the same as date in the dimensions (ex. + botton to make it quarter or when you put measures into it, it gives you an option to do quick YOY growth calculation, etc).

             

            Would there be another solution?  Thanks again.

             

            Regards,

            Jason