Excluding null targets from RAG Rating

[infock12]

Hi all,

Your expert opinion is appreciated. I have a pivot table with the following dimensions.

Site, Department and Median.

Median is the target, which I created through an inline table.

There are some departments which does not have a target. How do I exclude the Departments from RAG rating when they do not have a target please.

The below says, If(Results)<Median, Red, Green). But I wanted to say IF Median IS NOT BLANK, then apply the below formula otherwise leave it as Blank.

=If(sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png')

Hope it is clear and any help is appreciated.

Thanks,

Karthik

[trdandamudi]

I am not very clear on your requirement, but give a try with the below:

=If(Len(Median) > 0 And  sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png')

[infock12]

Hi TD,

This makes all the RAG rating blank, not sure why.

Your query is correct in logic. IF Median has value, then calculate. But we also wanted to say what to do if a Median does not exist for a department. Something like the below.

IF(Len(Median)>0 And sum(......) - this is the first part. The second part I am looking for is when IF(Len(Median) IS NULL

Alternatively, is there a way to say, only calculate the RAG rating when there is a value in Median?

Thanks

[infock12]

Just to make it more clear, I am looking for something like the below.

=If(Len(Median) IS NULL, 'blank', BUT IF(Len(Median)> 0 And  sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png')

[trdandamudi]

Just wondering why all the RAG's are blank...One reason you are getting blanks is because your Median do not have any value.

=If(Len(Median) > 0 And  sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png')

In the above expression I am saying, if length of Median is >0 And Sum (...) < Median. So I am guessing you do not have any value in the Median, Can you please check and let me know. If you think you have the values for Median, then can you please share a sample app to take a look.

Note: I am attaching a sample app based on your logic and see if that helps.

[trdandamudi · Accepted solution]

See if the below expression helps...

=If(Len(Median) =0, 'Blank',

IF(Len(Median)> 0 And  sum(CompletedAbs)/Sum(List)<Median, 'qmem://<bundled>/BuiltIn/arrow_s_r.png', 'qmem://<bundled>/BuiltIn/arrow_n_y.png') )

[infock12]

‌thanks TD. The Median has no values  for a few departments (Which is one of the dimension) but others have values. I will give it a go with the above and let you know.

[infock12]

Hi TD,

This works perfectly!! Thank you very much.

[trdandamudi]

Glad to know that the expression is working for you...