Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)
??
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
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)
)
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.
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)
)
??
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?
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
It Works! @sasiparupudi1, Thank you so much!!