Unable to solve missing parenthesis mystery

[Former Member]

Hello experts.

I am getting a multivalue error on one of my heat map cell. so I am trying to use following formula.

Its  is validating fine until the third statement which is

=Replace(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="High"))

;(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Low")))

;(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Moderate"))))

but I am receiving the error (Missing operator or closing parenthesis in 'Replace' at position 309. (WIS 10067)) as soon as I input the 4th condition. I verified all the parenthese opening an dclosing but still has the issue. can you some one please help me here.

Below is the formula which is erroring out

=Replace(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="High"))

;(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Low")))

;(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Moderate")))

;(ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Substantial"))))

[mhmohammed · Accepted solution]

Hi Rishi,

To simplify the issue with parenthesis, why don't you try to use If then Else combined with Replace?

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

Mohammed,

CAn you please elaborate, if I use if conditio does my cell gets populated with multiple values?

I Partially succeeded in displaying multiple values in the cell.as of now I have three and they are displaying fine but what if assesment change and the same cell should populate 5 codes(this is where i need to edit my variable to show atleast 5 values per cell at the max)

Sorry If I am confusing you.

[mhmohammed · Accepted solution]

Hi Rishi,

I may not know the whole scenario. Is that the whole formula that you have in your initial post?

One important thing I'd urge you to do before working on this formula further is break down the formula into different parts by creating variables

As Replace() functions needs 3 parameters

1. Parameter 1: String
2. Parameter 2: String to be replaced
3. Parameter 3: String to replace with

So, lets create variables for your formula as

1. v_Parameter1 = ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="High"))
2. v_Parameter2 = ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Low"))
3. v_Parameter3 = ReportFilter([Code] Where ([Residual Likelihood]="High" And [Residual Aggregate Impact]="Moderate"))

On that note, as you mentioned in your initial post that you start getting error when you add 4th condition. Question: How can you add 4th condition in a Replace, as it just accepts 3 parameters. I assume you're trying to add the next condition as 4th parameter in the formula, makes sense?

I think the formula you were trying to write (using the variable we created, it would look like)

= Replace (v_Parameter1;v_Parameter2;v_Parameter3), if you start adding a condition after v_Parameter3, you'll get an error.

Am I going totally OFF the topic? Or does it make sense? Let me know.

If you use If Then Else, you can add as many conditions as you want and do the Replace action in the Then part.

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

TThank you Mohammed.

I Am toyall not aware that replace accepts only three conditions.

but if I use the if condition does the cell display multiple values? As it has Else in that ?

[mhmohammed · Accepted solution]

Yes, you can display multiple values in the Else part, if you use the ReportFilter() function that you're using.

Thanks,

Mahboob Mohammed

[Former Member · Accepted solution]

THank you Mohammed.

I solved this by using just one reportfilter formula and with out Replace statement.

[mhmohammed · Accepted solution]

Ok, great.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Rishi,

what about the thrid parameter of the main Replace ?

Regards,

Rogerio

[Former Member]

Rogerio,

Thank you for the reply.

Until the third condition its working working fine. As soon as I enter more than three, I experiencing the issue.