topic Re: Excluding null targets from RAG Rating in QlikView

Excluding null targets from RAG Rating

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Excluding null targets from RAG Rating

trdandamudi — Fri, 04 Nov 2016 15:32:38 GMT

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')

Re: Excluding null targets from RAG Rating

infock12 — Fri, 04 Nov 2016 15:48:27 GMT

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

Re: Excluding null targets from RAG Rating

infock12 — Fri, 04 Nov 2016 16:00:30 GMT

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')

Re: Excluding null targets from RAG Rating

trdandamudi — Fri, 04 Nov 2016 16:09:11 GMT

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.

Re: Excluding null targets from RAG Rating

trdandamudi — Fri, 04 Nov 2016 16:19:50 GMT

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') )

Re: Excluding null targets from RAG Rating

infock12 — Fri, 04 Nov 2016 16:38:00 GMT

‌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.

Re: Excluding null targets from RAG Rating

infock12 — Sat, 05 Nov 2016 20:51:23 GMT

Hi TD,

This works perfectly!! Thank you very much.

Re: Excluding null targets from RAG Rating

trdandamudi — Sun, 06 Nov 2016 01:45:59 GMT

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