Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
catalystmichael
Partner - Contributor III
Partner - Contributor III

Section Access OMIT causes Invalid Visualisation error

We’ve all experienced pain when using section access.  Qlik Sense addressed one of the headaches by allowing you to right-click on an app and “open without data” in order to access your script if you lock yourself out.  However, Sense has introduced a problem that never existed in QlikView; that omitted fields cause visualisations to fail. 

I examine this in more depth on my blog https://adventuresinqlik.com/2018/03/11/the-problem-with-section-access-in-sense-with-workarounds/ but the basics of my suggested solutions are below...

In QlikView if you introduced section access and invoked OMIT fields (a list of fields which the user should not have visability of) any charts which contained omitted fields would still show up, just without the fields in question.  In Qlik Sense the chart will fail and display an ‘incomplete’ visualisation error.  Essentially, Sense has looked for the field and hasn’t found it because it has been omitted so it cannot render the chart.

Some possible workarounds: !

  1. Wait for Qlik to resolve the issue – show/hide columns has been mentioned as a roadmap item before but there can be no guarantees when/if this will find its way into the product.
  2. Use IF or Pick functions to create dynamic columns – after loading your section access tables load a new table for front end/UI consumption containing UserName and a flag to indicate if they should have fields hidden.  In the following example I arranged groups of users who had the same list of fields to be omitted into groups called OMITGROUP.  Create a variable in the UI to return the username=textbetween(OSUser(),’=’,’;’) & ‘\’ &upper(SubField(OSUser(),’=’,3)) and call it vUser.  Then create another variable to work out which ommission group they belong to  =only({<SA_USERID={‘$(vUser)’}>}SA_OMITGROUP) and call it vUserOmit.  You can then reference this variable in your chart or table =if(isnull(vUserOMIT),FieldToShow/Hide,Null()).  In this example the formula will hide the data in this field if the user belongs to any omission group but show it if they don’t belong to any omission group.  The drawback with this solution is the addition calculation time the above calculations add – best to test any impact on CPU.
  3. Split into 2 appsI’m not a fan of this approach but you could decide to create a non-sensitve data app and a sensitive data app and control who can access them via the QMC.  The reason I’m not a fan is the duplication of effort it takes to implement any changes to the apps.
  4. Put the sensitive fields into a separate table and show it alongside the original this could work if you ensure you have the same key field in both tables (e.g. customer ID), they are both sorted the same and there are no fields that cause duplication in one table but not the other.  That way your total number of rows will match up.  This wil work will when filtering for a particular result but will be a nightmare when scrolling through one table and not seeing the other move.  It would also prove problematic if you needed to export to excel (you’d need to run a vlookup to join the data back together).
  5. Use/build an extension with show/hide columns as a feature – if you or your client are happy to use extensions there are some out there with show/hide columns but you might still need to write in the formulas from solution #2 as your show/hide conditions.  Qlik Branch is the best port of call for looking at existing extensions.
  6. Use show/hide containers to house a version containing the sensitive fields and version without themThis is one example.  You could use a similar forula to that in solution #2 to determine which table they should see.
3 Replies
SA_VO
Contributor II
Contributor II

Hi Michael,

Thank you for the explanation, the second workaround you described works like a charm for 'simple' dimensions. I have an issue for calculated dimensions, where there I am already using aggregation, there the  =if(isnull(vUserOMIT),FieldToShow/Hide,Null()) has been difficult to implement.

Do you have a suggestion for a workaround for more complex dimensions? Is it possible to plug the vUserOMIT variable into the "Show column if" field?

SA_VO
Contributor II
Contributor II

Here answering my own questions: measures <> dimensions and measures, unlike dimensions, do not seem to 'crash' once the field gets omitted, the worst is that you will get NULL values or for some aggregations you might get some 0s. 
Michael's solutions works swell for the actual dimensions.

SA_VO
Contributor II
Contributor II

Piggy-backing on this thread again. For some reason, using the formula  =if(isnull(vUserOMIT),FieldToShow/Hide,Null()) does not work in one of my dashboards. I have found an easy workaround by using not(isnull(vUserOmit))  in the "show column if"  field.