Get Dimension into e() function

[Jaze]

I have the following case:

I work on a Table with a User-Role mapping and an other table with Role-Type-Mode. The Mode can be Include or Exclude. A User has different roles. A role allows or forbids access to a Type. A user gets access to Type, if he has a role, which Includes this type. But he will never get the access if he has a role which Excludes this Type.

As a result i want to have a table which counts how many user have access to a certain Type. In the example User1 does not have access to Type A, because he has both Role1 (Include) and Role2 (Exclude).

For the table in Qlik Sense I have the first column as a dimension with "=Type"

The second column is a measure with the following formula:

Count( distinct {< Mode = {'Include'}, Username= e({< Mode= {'Exclude'}>}) >} Username )

But the e() function is not considering in which row of the table we are. This results in wrong count. To calculate the correct value for example for the row Type = 'A' we can do:

Count( distinct {< Mode = {'Include'}, Username= e({< Mode= {'Exclude'}, Type = 'A'>}) >} Username )

But now the Type is fix to A, but i want to get the value from the first column of the table.

Thank you very much for help ❤️


PS: Btw,  i tested the following and it does not work:

Count( distinct {< Mode = {'Include'}, Mode -= {'Exclude'} >} Username )

Count( distinct {< Mode = {'Include'}, Username= e({< Mode= {'Exclude'}, Type = >}) >} Username )

Count( distinct {< Mode = {'Include'}, Username= e({< Mode= {'Exclude'}, Type = p(Type)>}) >} Username )

Count( distinct {< Mode = {'Include'}, Username= e({< Mode= {'Exclude'}, Type = {"$(Type)"}>}) >} Username )

Also First counting all users with Include and substracting all users with exclude does not work

[marcus_sommer]

It's not possible only with a set analysis because set analysis worked on a column-level and your requirement is to check if n values exists against a certain dimensionality which needs a comparing on a row-level. Or in other words - if value1 AND value2 exists and not only if value1 OR value2 is possible.

This means the data must be aggregated at first on the wanted dimensionality and could be then checked and counted - whereby it might be beneficial not to apply the conditions in a single calculation else to combine n ones - to count positive and negative separately and/or to add boolean checks against other ones.

Therefore an approach might go in this direction:

sum(aggr(
   count({< Mode = {'Include'}> } distinct Username) -
   (count({< Mode = {'Exclude'}> } distinct Username) * 2),
Type))

Depending on your real data you might need various adjustments - and be aware that it could become quite tricky.

Very important is further to keep attention on the association between the data respectively the relationship between the tables. They is n:m which is always difficult to handle. Personally I would tend to resolve such challenges within the data-model, for example by joining/mapping the tables at first and then applying n aggregations/filters/sorting and so on to get the wanted results.