From the course: Tableau 10: Mastering Calculations

Create level of detail expressions using FIXED - Tableau Tutorial

From the course: Tableau 10: Mastering Calculations

Start my 1-month free trial

Create level of detail expressions using FIXED

“

- [Instructor] In the previous movie, I showed you how to create level of detail or LOD expressions. Specifically, how to compare the value for a field, such as order total, for a specific row, as compared to the average of all values in that column. In this movie, I will show you how to make that comparison to a fixed value, that is an unchanging value, rather than the average of all values in the column. My sample file is the fixed workbook and you can find it in the chapter six folder of your exercise files collection. In this visualization, I have my order ID's and for information I also have my product name. My goal, is to create a calculation that shows how many days after the first time we ordered, say an office chair or a chest of drawers, we had to order the next one. I can make that calculation using the fixed keyword as part of a level of detail expression. So I'll start by creating a calculated field. I'll open the Analysis menu and click Create Calculated Field. And I will call my field First Purchase, and press tab. Now I can create my level of detail expression. I'll start by typing a left curly bracket, which is how you start all LOD expressions, then I'll type the keyword FIXED, which allows me to do the comparison to a fixed value, or a fixed record in this case. And I want to base my comparison on product name. Followed by a colon. To recap what we have so far, I have the fixed keyword, which indicates that I want to always make a comparison to the same value. And I'm going to be comparing based on the values in the product name field. Now I need to indicate the value that I will use as a comparison. And that will be the date of the first order. Which I can find using the MIN function to locate the earliest date within my order list. So I'll type MIN followed by a left parenthesis and then the order date field name. And I'll follow that with another right parenthesis to close out the MIN argument list. And then finally a right curly bracket to end the level of detail expression. So again, I want to compare the order date for a particular product name to the original, or first order date, for that same product. Everything looks good, calculation is valid, so I'll click OK, and now I can create a second calculated field, that uses the one I just created which is here, First Purchase. So, back to Analysis, Create Calculated Field, and I'll name this calculation Days Later, and press tab. Now all I need to do is to find the difference between the order date of the current order and the date of the first order. So I have order date minus first purchase. And that difference will be expressed in days. I'll go ahead and click OK and I see Days Later appear here under Measures. So I'll drag Days Later to the data area, and I get a bunch of zero's, because those are the first order. Or if I did have a duplicate, like here with terminal, they both apparently happened on the same day, then I see two for monitor, two for monitor again, and as I scroll down, the days after values get large. You can see here, that I didn't have to order catalog envelopes until order 395. And also notepads were first ordered on, or in, order 410. So as you can see, using the fixed keyword, allows you to make your comparisons in level of detail expressions to the first or perhaps the last value in a list.

Contents