Creating a 'static' visualization that isn't affected by selections within the app (can't use set analysis)

[xaelm]

I've created an AR dashboard that contains several reports that users can export, however I've ran into an issue. The reports are all contained in a "reports library" container on a separate sheet.

The issue is that these reports need to be 'static' reports that are filtered on a certain criteria, and ignore any selections made that would change the filter criteria.

For example, there's a report titled "X Coded Invoices" which is a simple table visualization and each record in the table contains both measures and dimensions. In this report, only records with an activity code of 'X' should be present, but the issue is: users can select records with different activity codes and as a result, the report would be inaccurate because it would contain records with an activity code other than 'X'.

I thought set analysis would be a good tool to use here, but the way I am filtering the records is by applying the following condition on the "Invoice Number" dimension: IF(ACTIVITY_CODE='X',INVOICE_NUMBER,null()) and not showing null records. To my knowledge, you can't use set analysis on dimensions, only on measures that are aggregated.

Any advice or best practices would be greatly appreciated!

Thanks!

[chriscammers]

I'm going to suggest a best practice that you will likely not find helpful but it goes to the root of your problem.

The suppressing null values on a calculated dimension is a problematic technique for exactly the reason you are pointing out. Best practice is to never use calculated dimensions rather use set analysis. I don't know anything more about your dashboard than what I read but I am sure you are wondering how to filter the chart so it only shows the invoices you want.

1. Create your chart, start with a table, and add the Invoice Number as the dimension and include whatever other fields you might need as  dimensions.
2. Create a measure that is applicable to your case Sum(Invoice Amount), Sum(Qty Sold), Count(Invoice Number) .
3. Add set analysis to your expression {<ACTIVITY_CODE={'X'}>}
4. in the chart properties find the "Add-ons" section and under the data handling sub-section you will find a checkbox "include zero values". Uncheck it! This will limit your chart results to show only records where all the expressions do not equal zero. So if you have multiple measures you do have to include the same set analysis on all the expressions but if you use different set analysis in another expression you will only see the invoices that have non-zero results for all of the expressions.

So this technique helps you because no matter what the user selects you will never see an invoice in your results that don't have the appropriate Activity Code since the user selection is overwritten by your set analysis.

By way of editorial, the "suppress zero rows" option was the default in QlikView and for whatever reason Qlik decided to set it to show all records. Since then, I see tons of users leaning on the "suppress when null" dimension setting which is problematic especially when you start talking about users making selections based on calculated dimension fields and searching for values in filter panes. I hope you take the time to adopt best practices here instead of working around the functionality.

Best of luck!

Chris