In Qlik Sense, the Count function, when combined with set analysis, can sometimes yield results that might seem unexpected at first glance. Let's explore your scenario:
With Year Filtered (2022): When you apply a filter for the year 2022, your formula Count({<Year={'2022'}>} Cars) correctly counts the number of 'Cars' in 2022. You mentioned this count is 25, which seems to be working as expected.
Without the Year Filter (2022 Unfiltered): The part that's causing confusion is when the year 2022 is unfiltered, and you still see a count of 6. This can happen due to a few reasons:
Set Analysis Context: Set analysis in Qlik Sense creates a set of data based on the condition provided. When you use {<Year={'2022'}>}, it explicitly instructs Qlik Sense to consider only the data from the year 2022, regardless of other selections or filters applied elsewhere in your application. So, even if you unfilter the year 2022, this expression still focuses solely on the 2022 data.
Data Model Issues: Sometimes, unexpected results are due to the way data is structured or associated in the data model. If there are any discrepancies or unexpected linkages in your data model, it can cause such issues.
Other Active Selections: If there are other selections active in your app, they might be influencing this result. For example, if there's a selection in another field that indirectly filters the data to a subset that includes some records from 2022, this can reflect in your count.
Data Integrity: Ensure that the data loaded into Qlik Sense is clean and consistent. Sometimes, data integrity issues can lead to unexpected results in analytics.
To troubleshoot this, you could:
Check Data Model: Examine your data model to ensure that the relationships between the 'Year' and 'Cars' fields are as expected.
Review Active Selections: Check if there are any other selections active that might be influencing this result.
Use a Table Object: Temporarily use a table object to display the 'Year' and 'Cars' fields to visually inspect what data is being counted.
Debugging Expressions: You might also want to use more granular expressions to debug, such as counting cars with additional conditions or displaying the distinct years for the counted cars.
By investigating these areas, you should be able to pinpoint why the count is not zero when the year 2022 is unfiltered.
Using the Max() function within your set expression is a way to dynamically determine the maximum year in your data set and then count the number of 'Cars' for that particular year. This approach can be particularly useful when you want the expression to automatically adjust to the most recent year in your data, especially in scenarios where new data is added regularly.
