Adding advanced filters using the Filter Syntax pane
When you create a filter, all of the variables in the filter expression must be at the level of the filter. However, you can include variables from a higher parent level by down-leving them. For example, you can create a single Person-level filter that is identical to the combined separate Person-level and Top-level filters described above by adding the NumRooms variable to the Person-level filter. When you use the Filter tab, UNICOM Intelligence Reporter automatically down-levs the NumRooms variable to the Person level if the level of the filter is defined as the Person level. If you display the Filter Syntax pane, you will see the filter expression is as follows:
gender.ContainsAny({Female}) AND ^.numrooms<8
The numrooms variable is preceded by the down-lev operator (^.). This filter selects women and girls who live in households that have less than eight rooms, just as the combination of the two separate filters does.
You can include variables from a lower child level in the Filter Syntax pane by using the syntax for up-leving them. For example, if we wanted to create our filter at the top level, we could up-lev the Gender variable. However, it is not possible to create the previous filter at the top level. We can either select households that contain at least one female and less than eight rooms:
SUM(person.(gender.ContainsAny({Female}))) AND numrooms<8
which gives this table:
Table showing region by number of rooms, with filters
Or we can select households that contain no males and less than eight rooms:
Sum(Person.(Gender = {Male})) = 0 And NumRooms < 8
which gives this table:
Table showing region by number of rooms, with filters
However, it is not possible to create a top-level filter to select females only, because that information is not available when you up-lev the data to the household level. The reason for this is that all of the Person-level data for each household is collapsed together.
The syntax for up-leving data is the up-lev operator ( .( ) in combination with one of the aggregate functions supported by the Data Model (Sum in this example).
When creating multiple filters at different levels for individual tables, the filter levels must have a direct parent-child relationship with each other and not be parallel to each other (on different branches of the tree). For example, using the Household sample data, you cannot create separate filters at the Person and the Vehicle levels for the same table, because these levels are parallel to each other. However, you can create separate filters for the same table at the Person level and Trip levels, because the Trip level is a direct descendent of the Person level.
If you need to filter a table on variables from parallel levels, you must create the filter at the first common ancestor level and up-lev the variables from one of the levels to the first common ancestor level and then down-lev the data to the level of the filter. If you are working in the Filter tab, you simply need to set the level of the filter and then select the variables, and UNICOM Intelligence Reporter takes care of the up-leving and down-leving for you.
However, when you are working in the Filter Syntax tab, you need to specify the expression correctly. Here is an expression for a Person-level filter that uses the vehicletype variable from the parallel Vehicle-level:
Gender = {Male} And ^.Sum(Vehicle.(VehicleType = {Motorbike})) > 0
See also
