Comparison filters allow you to compare data within two columns and to filter the comparison of those two columns. These filters compare the values stored in the columns, not the label of the columns.
Adding a Comparison Filter:
In this example we want to see all records in which a record has "Yes" for the field "Blue" and "Yes" for the field "Red" Note: You will notice in the screenshot that, in green, you see "Blue Equals [Red]" This is not literally asking the report "does blue equal red?" but rather, it is asking "does the value in the blue column match the value in the red column?". We can see that this is true because "Yes" equals "Yes" in this example.
1. Click the "+" sign to add a filter
2. Check the Comparison box
3. Pick the column from the report, Blue, and then choose how we would like to compare it. In this case, we want to know what is Equal. Then, pick the other column that you want to compare it to, in this case it is Red
4. Click Apply
Hints and Tips
- There are many different reasons why you would want to compare. Comparison filters can be used in conjunction with aliases to compare a pre-test score and a post-test score, for example.
- You can only compare columns that have the same type of values. For example, Date columns can only be compared to other date columns and numeric columns can only be compared to other numeric columns.
- If you have a scenario where you need to compare a date column to a text column there is a workaround. You may wonder why we would do this: you may have been tracking dates incorrectly on a form using a text field, but typing them in the correct format of DD/MM/YYYY. You could then use a "Special Column" of Text Calc to combine the Date field and a static, blank value. Then one more text calc column to combine the text column and a static, blank value. You could then compare the two text calc columns because they are of the same type.
- Another example would be if you want to see results where Starting Currency is "Less Than" Ending Currency, this would show anyone who has gotten a raise, for example.
- Capitalization is ignored. For example, "No" equals "no" and "YeS" equals "yes".