Sign in or start a free trial to avail of this feature.
2. Using Fixed in Logical Functions
When logical functions are combined with filters, errors can often occur. To fix this, we can use the FIXED() function to keep our calculation constant, despite the presence of Quick Filters.
To explore more Kubicle data literacy subjects, please refer to our full library.
Why use FIXED() in logical functions
- Filters can often upset the conditions that you have created in an IF/ELSE function
- If you want your calculated field to ignore Quick Filters, used the FIXED() function
- FIXED() function will allow filters to work on your charts but will not affect your IF/ELSE calculations
In the previous lesson, we run into problems with our product size calculated field. When the filter was not applied and the full date range was included we had the correct assignment of small, medium, large and very large products. However, when I adjusted the date range this assignment changed. So how do we fix this particular problem, which tends to be a common problem for if and else statements? Let's jump back into our formula.
So I'll click on the dropdown and edit.
And in here, we can see that the problem is with our condition and some of sales, which tends to change with the data that's included or not included in the view. We need to fix this particular value so that it doesn't change when filters are applied. To do this, we're going to use a function called fixed, which is one of four level of detail expressions that will be explored further in a later course. To write a fixed expression, I start by opening parentheses or a curly bracket, and then writing fixed. I then need to set the dimension at which I want to fix on, which would be a product name, then I write a colon and the expression that I want to fix, which in this case is sum of sales.
Then I close my parentheses and unfortunately I'm still getting an error. When I click on the error, I get my usual non-aggregate comparison. So to solve this or wrap my expression in an attribute function.
And this removes my problem, and I'm going to take the same expression and apply it to the else if statement.
There we go. Now I'll press OK, and adjust my filter. As I scroll back and forth, I can see that the values don't change for the individual products, which is exactly what I want.
In this particular calculation I've used the attribute function a couple of times, and it's worth exploring what this function does. I basically use the attribute function when I have a column or non aggregated data that's all the same value, and I simply want to turn this single value into an aggregated number. Attribute is simply a check for uniformity. If all the values in the field or calculation are the same, then it returns that value. Otherwise it returns an asterisk.
An if else statements, it's a great way of fixing the non aggregated data error that we got earlier. Be it for text or for numerical data. The next time you run into this type of error, see if the attribute function can help you out.