The FIXED LOD expression, computes values using the specified dimensions without reference to any other dimensions in the view. These expressions are most useful when fixing a dimension regardless of what is and what is not part of our view. In this case, the results are computed at a granularity that is independent of the fields used in the visualization's level of detail.
The syntax of using FIXED LOD expressions is FIXED [Dimension] : aggregate expression}.
The preceding syntax shows how one can use a FIXED expression for a single dimension; however, if there is more than one dimension that we want to include in our calculation, then the syntax can be modified as {FIXED [Dimension1], [Dimension2], [Dimension3] : aggregate expression}.
Let us see how we can use the FIXED LOD expression in the following recipe.
Let us use the Customer Name and Order Date fields from the Orders sheet of Sample - Superstore.xlsx data to find out how we can compute the first purchase date of a customer regardless of what else is in the view.
- Let us create a new sheet by pressing Ctrl + M and rename it as
FIXED LOD. - Let us drag Customer Name from the Dimensions pane and drop it into the Rows shelf. We will then right click and drag the Order Date field from the Dimensions pane and drop it right after the Customer Name field into the Rows shelf.
- We will then select the MDY(Order Date) option. This will give us all the dates when a particular customer purchased from us.
- Next, let us find the First purchase date of each of these customers. In our previous recipes, we have already created a First purchase date. Let us drag that field and drop it into the Rows shelf, just after the MDY(Order Date) field. This will give us the exact dates of what is shown in the MDY(Order Date) column, instead of giving us one date per customer, which is the First purchase date of that customer.
- This is because our First purchase date calculation is simply taking a minimum of Order Date as the formula that we used earlier, which was MIN([Order Date]).
- Now if we remove the MDY(Order Date) field from the view, we will get one date, that is, their first purchase date per customer. However, in the current view that isn't happening. So, if we want to retain the current view and still be in a position to show one date, that is, their first purchase date per customer, then we will create a new calculated field and name it First purchase date fixed @ customer. The formula will be {FIXED [Customer Name]:MIN([Order Date])}.
- After clicking OK, let us right click and drag the First purchase date fixed @ customer field from the Dimensions pane and drop it into the Rows shelf. Let us select the MDY(First purchase date fixed @ customer) option. Our view will update, as shown in the following screenshot:
In the preceding recipe, when we get the First purchase date calculation, it takes into consideration all the granularity that is there in the view and hence we get the exact dates as of the MDY([Order Date]). Whereas, when we created First purchase date fixed @ customer, we essentially made Tableau take the minimum Order Date at a customer level by ignoring all the dimensions in the view.
To know more about FIXED LOD, refer to https://onlinehelp.tableau.com/current/pro/desktop/en-us/calculations_calculatedfields_lod_fixed.html.