4 Replies Latest reply on Oct 24, 2014 11:40 AM by Lori Johnson

    date

    Lori Johnson

      I need well behaving dates including time that will allow me to filter the values in variety of times such as by month, by week, by am or pm etc. I don’t need to see the year since all of my values are in 2014. Also, I don’t want to see the values aggregated.

       

      Thanks!

        • 1. Re: date
          Jonathan Drummey

          Hi Lori,

           

          I'm not clear on what your goal is with this data, having a mockup of what you're trying to do. There are several different ways to build a date field that will work as a date in Tableau and you can choose the aggregation you want:

           

          Screen Shot 2014-10-23 at 10.11.45 PM.PNG

           

          The first batch of options are what Tableau calls a Date Part, they are just the month, week of the year, etc. The second batch of options are for a Date Value, they return a date truncated to the chosen level. You can set up your view with whichever of these you like, and multiples of them. As for filtering on AM/PM, there's already a dimension for that in the data that you can use.

           

          An additional date level option is Exact Date (the one selected in this case), that uses the exact values of the date dimension. Note that when you choose one of the date value or Exact Date options, Tableau's default behavior is to convert the pill into a continuous (green) pill and that draws an axis. If you want headers, you can turn that back into a discrete (blue) pill from the pill's menu.

           

          I constructed the Structured Date dimension using the following formula: DATETIME(FLOAT([Date]) + FLOAT([Hour]) + IIF([Am/Pm]=="p",.5,0))+2. The +2 is needed as an offset between Excel's epoch date and Tableau's epoch date. The date dimension could have been made using DATEPARSE() or a set of strings, there are many ways to work with dates in Tableau. Note hat

           

          You'd said you didn't want the view aggregated, which I'm guessing to mean that you want to see all the values of your Date. I set that up using the Exact Date date level in the not-so-dis-aggregated view. However, that is not a truly dis-aggregated view, because there are two records with a date of 10/14 2:46.

           

          Also, you said you don't need to see the year. You can change the formatting of the date's pill to remove the year, and make other changes as well:

           

          Screen Shot 2014-10-23 at 10.22.00 PM.PNG

           

          Workbook is attached. I hope this is helpful!

           

          Jonathan

          • 2. Re: date
            Lori Johnson

            Hi Jonathan,

             

            You have been soo helpful! You provided me with a good foundation to find my way around. By de-selecting the 'aggregate measures' option on the analysis menu, I got rid of the aggregated values.

             

            I kept am and pm value type as string and filtered. I've got what I needed.

             

            Is there any way you can explain your formula elements in a simplified way?

            DATETIME(FLOAT() + FLOAT() + IIF(=="p",.5,0))+2 

             

            Why are date and hour "float"?

             

            I am sending your way an extra 1000 points which you deserve :)

             

            Thank you so much!

            • 3. Re: date
              Jonathan Drummey

              Hi Lori,

               

              You're welcome!

               

              Dates are stored as numbers, for a DateTime field it's a decimal number where the whole number is the number of days from some epoch date and the fractional part is the hh:mm:ss.xxxx. So, for example, if the epoch date is 1/1/1900 and the datetime value is 1.75, that translates into 1/2/1900 18:00 (since 6pm is 3/4 of the way through the day). For Date field the underlying number is just an integer. Date formatting then resolves that to an output like mm/dd/yyyy hh:mm, or whatever you like.

               

              One way to work with dates is to use the DATEADD, etc. functions, another is to work directly with the numbers. So the FLOAT() casts the Date and Hour fields back into their underlying numbers and adds them together. Because the fields are coming from Excel that has an epoch date of 12/30/1899 and we're working with Tableau that has an epoch date of 1/1/1900, it's necessary to add 2 days to get the right date. The Hour field is stored in 12 hour format, so to get the right AM/PM value we have to add 12 hours for the PM numbers, and IIIF() statement does that by adding 0.5 (half a day).

               

              Does that make sense?

               

              One word of caution on using Analysis->Aggregate Measures->Off: if you want to do further aggregations (like with table calcs), you can end up with some strange outcomes. In my data sets I try to have some sort of unique Row/Record ID and can bring that dimension into the view when I need record-level results.

               

              Jonathan

              • 4. Re: date
                Lori Johnson

                Hi Jonathan,

                 

                I am not a real newbie with Tableau but DATES have not been a part of my vizes and dashboards so far. So I am entirely a beginner for formatting dates.

                 

                Your explanations introduced me to DATE functions and formatting. After reading a couple of times, they made sense. But I feel I need to practice a little further, so please watch out for my questions on the forum in the future.

                 

                As for your caution on using Analysis>Aggregate measure>off, thank you, and I will ask about it in a separate posting.

                 

                Great help; I appreciate it!