4 Replies Latest reply on Jun 7, 2018 5:53 AM by Matthew Yandell-Thomas

    Vector file - Field names are dates

    Matthew Yandell-Thomas

      Hi everyone,

       

      I'm brand new to Tableau and wondered if someone could help with a problem I'm experiencing.

       

      I have a vector file which contains field names based on dates and the values within these fields are floats. I'm trying to plot a line graph of each feature to show change over time. However, it's plotting each value an equal distance apart as opposed to accurately plotting each to it's date range.

       

      Here's an example dashboard and plot that I created during the trial period:

       

      ExampleDashboard.PNG

       

      ExamplePlot.PNG

      In this example there is no February or July but the points are same space apart.

       

      Here is an example of the current data I'm working with:

      ExampleData.PNG

      I changed the field names to be entirely in a date format instead of including any other strings like the last example. The date format is in yyyymmdd.

       

      Is there anything I can do to make Tableau recognise that these field names are supposed to be dates?

       

      Also, it's currently summing the measure values together. Is it possible to have each individual feature displayed as opposed to grouping everything on the same date together? Or should I ask that as a different question?

       

      Thanks in advance,

       

      Matthew

        • 1. Re: Vector file - Field names are dates
          Mavis Liu

          Hi Matthew,

           

          Tableau isn't recognising these as dates because each of those 'dates' is a type of measure. The data needs to be pivoted and restructured so that Tableau can read it in the way it needs to. Essentially if you have data like this:

          2018-06-07_12h20_20.png

           

          You need to do a shift/control to highlight those year columns and right click to select pivot:

          2018-06-07_12h20_38.png

           

          So that you end up with a column for dates:

          2018-06-07_12h21_46.png

           

          By default, these field names will be Pivot Field Names and Pivot Field values, but you can right click and rename them. Clicking on the Abc button for the date columns means you can convert the data type to Date.

          2018-06-07_12h22_37.png

          Once you're in the worksheet, you can now bring this date field into the view and it will be recognised as a date. I'd recommend also using the continuous dates (highlighted in green) so that you get a continuous axis and don't have gaps where you don't have the months in your dataset.

           

           

           

          2018-06-07_12h23_52.png

           

          Thanks,

           

          Mavis

          1 of 1 people found this helpful
          • 2. Re: Vector file - Field names are dates
            Simon Runc

            hi Matthew,

             

            So you should be able to force Tableau to create a date using the DATEPARSE function (although this isn't available for all data-sources...although you can always extract data to a .tde/.hyper and you'll get all functions)

             

            Some thing like

             

            DATEPARSE('yyyyMMdd',[YourDateFieldAsAString])

             

            With regards dis-aggregating the data. You can either drop the "features" dimension onto the detail tile to get a line per feature (or on the colour tile to get a different coloured line per feature)

             

             

            or you can choose to dis-aggregate everything by unticking Aggregate Measures (in the Analysis Menu)

             

             

            Hope that does the trick, but let me know if not

             

            ...Just seen Mavis' reply..I hadn't clocked that your dates went across the page! You'll need to de-pivot them (as she suggests and then Tableau should have no issues picking up the date format). This will also make your Tableau life much much easier in lots other ways (eg. the Viz you've built can be done in 3 drags!)

            1 of 1 people found this helpful
            • 3. Re: Vector file - Field names are dates
              Tim Dines

              Mavis, your answer is very well presented.  Great job.

              • 4. Re: Vector file - Field names are dates
                Matthew Yandell-Thomas

                Hi all,

                 

                Thanks for the replies.

                 

                Mavis. This solution worked for me. I just had to do some digging as initially the pivot option wasn't available to me. It appeared after writing the attributes to an Excel file and linking the vector based on the unique ID. Thanks for taking the time to make such a detailed reply.

                 

                Simon. I found the DATEPARSE function on a blog post but it didn't seem to behave as expected. This is something for me to look into when I'm more confident with using Tableau. Thanks for suggesting to drop the features into the Detail box. I don't know why I didn't do that! Perhaps seeing SUM in the pill for the values threw me off and made want to set that to unique or individual values.