1. Calculated Fields to extend data
We're going to continue to expand the columns by adding two calculated fields.
As practice, we’re going to assume that we weren’t given the field tripduration. Let's use the drop down caret at the top of the data pane as just another point of entry for creating calculated fields. And for this particular one, it'll bring us an empty dialog box to work with. We're going to be creating tripduration in hours.
Inside this dialog, I'm going to be using the function DATEDIFF and you'll want to tab into these as they popup. The date part is what segment of the time and date stamp we want to use. Date part can take many different values, including ‘year’, ‘quarter’,’week’ to ‘minute’. We want to extract hours. Note that we put the datepart in single quotes.
And again we’re looking at start time and comparing it with end time. I’m selecting them from the list through tabbing. This calculation is valid, so I'm going to save it. And I’m going to bring it into my list of measure values. Let’s change that aggregation from sum to average.
Now, notice that a lot of them are zeroes because they're fractions of hours. So that speaks to what we always need to do anyway, which is to change the default properties. Wel’ll use number format and then number custom. We'll go ahead and leave two decimal places in place and we'll give it a suffix for the hour.
Now in our second calculated field, we want to be able to see weekend versus weekday. Let’s call it weekday or weekend.
We’re going to use the logic structure of IF THEN ELSE along with Date Part to test if a given date is a Saturday or Sunday. To get this done, we’ll use date part and we’ll ask it to extract weekday. Day of the week is what weekday means here and it will return a value from 1 to 7, where 1 represents Sunday and 7 represents Saturday. So we’ll say if the date is 1 or 7, we’ll have the column display “weekend”.
Otherwise, we'll want it to say weekday. There are other ways to structure this formula. But I chose this way because it's easier to test for two day values than for five so that's the logic there.
The calculation is valid so now I’ll save this. It has popped up here with the other measures dimensions that can be used to slice and dice the data. Let’s see if this works by right clicking on it and telling it to show the filter. It pops it into my filter shelf and it also creates a legend over here in the right-hand pane.
The final touch here would be to add a customization to it, which is the show apply button. It gives me more time to change what's here and then say apply without the content of the visualization disappearing just because you don't have something selected. It's a little smoother of a transition for your users and it’s more professional. Ok, time for you to try it out!
2. Let's practice!