In this video, learn how to return the current date, or date and time, within a visualization.
- [Instructor] Businesses run on schedules. You need to know when something is due so you plan far enough in advance to get it done on time. In this movie, I will show you how to return the current date or the current date and time so that you can create calculations that tell you how long you have to work on a project. My sample file is the return current date workbook and you can find it in the chapter four folder of your exercise files collection. This workbook contains a worksheet that has a list of clients, and those clients have requested proposals for conference hosting at my hotel by a certain date.
And, what I want to do is to determine how long I have to turn those proposals in. You'll notice I haven't added the dates yet, and that's because adding the dates can be a little bit tricky, so I wanted to go through it in detail. The first thing I'll do is go over to the data area, the data panel, and I will drag over proposal required, which is a dimension. So, I will drag that to the data area, and you can see that it's displayed as year of proposal, that's what that says, year of proposal required.
That doesn't really help me because if I click the show detail button here on the your proposal pill on the rows shelf, then I get the quarter, that doesn't help me, if I click that then I go to month and that doesn't really help because I'm taking up a lot of space. So, I'll go ahead and hide the detail all the way back to year, and, instead, I will change my data from a dimension to an attribute. So, I will click the down arrow here at the right edge of the year pill, and I'll go down and click attribute, and you can see now that I have my client name and proposal required, so that's displayed correctly.
Now, I can create my calculated field that will tell me how much time I have left, and I can do that either using the current date and time or just the current date. I'll show you how to do both and I'll do it in the same field, just editing one after the other. So, I'll go to the analysis menu and click create calculated field, and for the calculation name I will call it time until due, and then press tab.
Now I can calculate the amount of time I have left by subtracting the current date from the due date. So, I'll start doing the current date and time, so that is the proposal required field, I start with that, then a minus, you can subtract one date from another to figure out how long you have, what the difference is between the dates, so I have proposal required, minus and then the function for the current date and time is now, followed by parentheses.
Everything looks good, I am subtracting now the current date and time from when the proposal is required, and I'll click OK. So, there we go, I have a measure, time until due, and I will add that to the text mark. So I see I have 29.32 days until this proposal is due, 26.24 for this one and a bunch of others, all of which are at least four weeks out except for BSMG, which is about a day and three-quarters sooner.
If I want, I can also just return the current date and use that in my calculation, so I will go back to analysis, point to edit calculated field and click time until due, and, here, instead of using it now, which gives me both current date and time, I'll just do today, that's just current date, so I'll click OK, and my sheet is updated. You'll notice that I have the number of days and then the portion of the day, and I can use the decimal to determine about how much time I have, but the really important measure is the number of days here, which are to the left of the decimal point.
- Defining data types and conversions
- Defining function syntax in Tableau 10
- Creating a calculated field
- Creating an ad hoc calculation
- Summarizing data using aggregate functions
- Calculating values using number functions
- Finding the absolute value or sign of a number
- Working with times and dates
- Creating IF THEN expressions
- Managing null values using IFNULL and ISNULL
- Creating level of detail expressions
- Creating a quick table calculation
- Converting and measuring strings