5 Replies Latest reply on Nov 2, 2016 8:40 AM by Bill Lyons

    Issue with dates after pivoting: null values

    SIlvina Monteleone

      Hi everyone,

      Im struggling with dates filtering here. I want to make a timeline to see profit variation among a couple of projects.

       

      Tableau created a separate measure field for each month, so I followed this thread that had exactly the same problem: My data source creates a separate measure field for each month's data. I want to be able to link these to a date field, how do I do it?

       

      These are the steps Bill Lyons kindly provided (thank you!)

       

      This is actually a great example of why Tableau 9.0's data interpreter is so wonderful. I created a 1 row sample table in Excel with fields named as you show above, with random data values, connected to that in Tableau. What we want to do is "pivot" the columns to be rows. Steps:

      1. Connect to Excel file and open in Data Source Editor.
      2. Select all of the columns to be pivoted. Click on the first column to pivot, in my example, "Actual Aug 2016", then hold down shift, and click on the last column to pivot, in my example, "Actual Jul 2017".
      3. Mouse over the area next to one of the column labels, and a drop-down arrow appears. Click that arrow and select "Pivot" from the menu. All of those columns should now become 2 columns: "Pivot field names" and "Pivot field values."
      4. Switch to Sheet 1, and create a calculated field called "Date" containing the formula: DATEPARSE('MMM yyyy',MID([Pivot field names],8)).
      5. Double-click "Pivot field values" then double-click "Date".
      6. Change your Date pill to the appropriate interval (i.e. month).

       

      I followed through and I still cant seem to get it right, I've pivoted the dates and created the calculated field (looks good) but I get null values or strange years (December of 1899??) if any of you could give it a look I would appreciate it.

      I'm leaving the office now kind of burned off, maybe its more silly than what I think.

       

      PS: I am using Tableau 9.2

      Dates.JPG

      Thank you

      Sil

        • 1. Re: Issue with dates after pivoting: null values
          Sherzodbek Ibragimov

          Can you please show your data with dates in data source? Depending on how your dates presented you may have to use different functions.

          sorry i was texti from my phone so I can't see your file

          1 of 1 people found this helpful
          • 2. Re: Issue with dates after pivoting: null values
            Bill Lyons

            Thank you for including your packaged workbook. It really helps us help you, by seeing your data, your calculated fields, etc. And thank you for stating your version number, that saves us the trouble of trying each version to find which one you used.

             

            There are a couple of problems with your Date calculation DATEPARSE('MMM yyyy',MID([Pivot field names],8)).

             

            The first problem in your calculation is the use of MID([Pivot field names],8). This returns only the 8th and subsequent characters in the string, so which is meaningless in this case. See the field [Use of MID] in Sheet 2.

             

            The second problem is your use of the DATEPARSE() function. Your date strings are in the format "1/1/2015". DATEPARSE() uses the definition of date formats at Formatting Dates and Times - ICU User Guide, so 'MMM yyyy' would match a string like 'Jan 2015'. If it can't match the date, DATEPARSE() will return NULL. To match '1/1/2015' the calculation should be one of these:

            If your dates are in the format month/day/year, then: DATEPARSE('M/d/yyyy',[Pivot field names])

            If your dates are in the format day/month/year, then: DATEPARSE('d/M/yyyy',[Pivot field names])

             

            Based on your formula using "MMM yyyy" I am guessing you are using the day/month/year format. See the field [Date corrected], which assumes this format. (My system is configured for month/day/year, so I had to modify the default format to display the date as day/month/year with no time.)

             

            However, if the date format is the default for your system, then there is likely a much simpler way: just change the data type. Tableau should be able to automatically recognize the date. To illustrate this, I duplicated the field [Pivot field names], creating [Pivot field names (copy)]. Then, I just right-clicked, and chose Change Data Type -> Date. Because my system is configured for month/day/year, that is how it interpreted your dates, but if you do this in your system, it should interpret it based on your locale settings.

             

            Finally, it makes things much simpler if you rename the field [Pivot field names]. Just right-click and Rename.

             

            I hope that helps. Please see my attached example, also in v9.2.

            1 of 1 people found this helpful
            • 3. Re: Issue with dates after pivoting: null values
              SIlvina Monteleone

              Bill Lyons Thank you for your quick solution to help me I was able to make it super easy with your explanation. I see I have a lot to learn.

              Im sending a screenshot of a couple of charts i've played with since I applied your solution.

               

              Promise to look more deeply into date formats, luckly I'll be able to help someone in the future.

              Date issue resolved.JPG

              Cheers!

              Silvina Monteleone

              • 5. Re: Issue with dates after pivoting: null values
                Bill Lyons

                Great! Glad it worked, and glad you learned something!