Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community, I have the following example. In a pivot chart I have a field "1" and a field "2" that contain a list of roles. I am needing to show the pairs of roles that result from combining the possible options, but with the condition that the role appears in the list of field "1". If the role of field "2" does not exist in field "1" I am not interested in showing that line in the table.
for example in field "2" from qv file attchment, the roles: Role 1, Role 21 and Role 22 does not exist in field "1". I need to exclude those cases when the text string does not appear in dimension 1.
It doesn't work for me to exclude it from the script since for example Role 1 I need it in the data model. The information is a dummy example but the background analysis is about segregation of duties. So it helps me to see it from a pivot chart.
Thank you very much for your help.
Eduardo
Hi, you can just add the expression in an If:
If(Sum({<[Field 2]=P([Field 1])>} 1)>0, sum(Risk_Level))
I also noted that selctions in [Field 2] where overwrited by set analysis so maybe you want to add a '*=':
Sum({<[Field 2]*=P([Field 1])>} 1)
HI, any of this 2 options can work:
- Sum({<[Field 2]={$(=Chr(39) & Concat(DISTINCT [Field 1], Chr(39) &',' & Chr(39)) & Chr(39))}>} 1)
- Sum({<[Field 2]=P([Field 1])>} 1)
Thanks @rubenmarin ,
In principle for the example it worked!
Hi, you can just add the expression in an If:
If(Sum({<[Field 2]=P([Field 1])>} 1)>0, sum(Risk_Level))
I also noted that selctions in [Field 2] where overwrited by set analysis so maybe you want to add a '*=':
Sum({<[Field 2]*=P([Field 1])>} 1)
Works fine!
Thank you @rubenmarin