Filter pane - filtering when there are two dimension tables?

[user467341]

Hi Qlik community,

I have the following data model in Qlik.

I created a "Filter Pane" visualisation to filter the selection. The filter pane is like this:

Following users belong to the department:

• User 111111 - Supermarket
• User 222222 - Clothes
• User 333333 - Supermarket
• User 444444 - Bank

When I select "Supermarket" in the "Department" filter pane, the "Employee" filter pane do not filter user 111111 and user 333333 only. Instead, it gives me other employee ID together with the two supermarket filter (I am getting more than two results).

The "Centre_Dept" is not filtering the "DIM_Employee" field "Mall_CentreDept". Is there in set expression to filter this or is my data model wrong?

Do take note that "Department" filter pane has a set expression to get a certain subset of data (eg the department you saw in the image above is filtered by "=Aggr(Only({1<Centre_Name= {"Commercial*"}>} [Centre_Dept]), [Centre_Dept])").

My objective is to filter by department, to get the employee ID in that department and ultimately filtering the FACT table.

Thanks.

[Dalton_Ruer · Accepted solution]

I recreated your structures and when you click on a value in the filter Qlik does limit the employees.

However, without applying a filter, all employee records show up because limiting the display values in a filter for centre dept isn't the same thing as actually applying a filter to the field. If you want to limit the Employees filter box display values then you would also need an expression to limit them. Like:

=Aggr(Only({1<Centre_Name= {"Commercial*"}>} EmployeeID), EmployeeID)

[user467341]

Hi Dalton,

Thanks for your reply. From your image, do I have to create two more filter dimensions in the filter pane object for this to work?

I did tried the the aggr expression in the DIM_Employee, but it is filtering and returning more employee ID than it should be (eg filter by  Supermarket where there are 2 employees, however the filter returns 3).

I feel that the employee ID is not filtering properly because when I filtered the Centre_Dept, it does not properly filter the employee ID in DIM_Employee since the field name is MallCentre_Dept (perhaps it is not filtering from the selected Centre_Dept in the first filter option). Hence, the FACT table is not filter properly.

Is there a way in set expression that I can filter Mall_CentreDept based on Centre_Name = Commercial*? Essentially it is from two different dimensions, like a cross dimension filter criteria to get the result in FACT? This is my thought process of the flow:

1. First filter - filter by "Centre_Dept", eg select "Supermarket"
2. Second filter - EmployeeID filtered to only two options, which are 111111 and 333333 as these two employee belongs to department "Supermarket". End user may select either one or all. Note that the display for this filter pane should have already been filtered with Centre_Dept = "Commercial*" before further filtering by the first filter.
3. Final result - there is a fact table, which can be either filtered from first filter, second filter or both to display the results.
4. End

[user467341]

After doing a few testing and checking the source data again, I finally found out why my filter pane is not able to filter properly.

Thanks to Dalton for showing that under normal data, it will work naturally. I will mark it as solution. 🙂