Hide Columns expressions - Calculate KPIS

[educastri83]

Hello community!

This time I am working in a matrix of segregation of duties.

I added the matrix model to a dashboard in Qlikview, but I can not interact as I would like when I select some dimensions ... for example, it shows all the expression columns and should not appear when the group does not exist in the "Role" dimension "selected, at the same time that I would like to be able to build high, medium or low incompatibility KPIs

I have these challenges:

1) I need to be able to calculate some KPI's for each group: the high, medium and low incompatibility risks.

2) Hide columns of the expressions, showing only those groups that exist when they select some value of the dimensions such as "role", and "user". Something like this:

To calculate the KPI of high risk incompatibilities, create a variable named "vHighRisk" with the following calculation:

SUM (Aggr (if (sum ([Vendors Master]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Purchase Order Entry]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Physical Inventory]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Sales Agrmts / Contracts]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Customer Master Maint.]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Sales Invoicing]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Sales Invoice Release]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Sales Order Entry]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Sales Order Release]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Sales Pricing Maint.]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Maintain Security]) = 3,1,0), Group))
+ SUM (Aggr (if (sum (Payments) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Bank Reconciliation]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Cash Application]) = 3,1,0), Group))
+ SUM (Aggr (if (sum ([Search Operations]) = 3,1,0), Group))

The above works well if it stays static, but when selecting another dimension the result is not updated, it is not the desired result, I would like to get a better practice

Thank you all

[sasiparupudi1]

May be try like below

for high risk

RangeSum(
If(Column(4)=3,Column(4),0),
If(Column(5)=3,Column(5),0),
If(Column(6)=3,Column(6),0),
If(Column(7)=3,Column(7),0),
If(Column(8)=3,Column(8),0),
If(Column(9)=3,Column(9),0),
If(Column(10)=3,Column(10),0),
If(Column(11)=3,Column(11),0)
)

[educastri83]

Thanks @sasiparupudi1 

I already tried with the expression but added the value of the columns, for example 3 + 3 + 3 ... = 9 and I need to count the times that are high risk... and so for the medium risk.

[sasiparupudi1 · Accepted solution]

RangeSum(
If(Column(4)=3,1,0),
If(Column(5)=3,1,0),
If(Column(6)=3,1,0),
If(Column(7)=3,1,0),
If(Column(8)=3,1,0),
If(Column(9)=3,1,0),
If(Column(10)=3,1,0),
If(Column(11)=3,1,0)
)

??

[educastri83]

Yes, count the columns that have high risk. Thanks

The next step to achieve is that when interacting with the values of the "Role" or "User" list boxes, the incompatibility matrix only shows the columns that have those selections, and not all the columns as it is currently displayed.

Any suggestions?

[sasiparupudi1 · Accepted solution]

If my understanding is correct, when a role /user is selected, there will be a filtered list of applicable groups.

so if you want to only show the expressions that are related to the possible groups, you need to use conditional show/hide

for example a user role admin is chosen, vendors management group is visible so you could put a condition for the  column U01 Vendors Master

=If(SubStringCount(concat(Group),'Vendors Master')>0,1,0)

so on soforth..

I hope this helps

[educastri83]

It Works! @sasiparupudi1, Thank you so much!!