Compare two ranges

[#1]

Hello,

I need some help.

I load from source a table called Measuring with 3 columns: Gauge, Measure and Target.

See attached file.

I want to know the amount of gauge's that's are under the target, On target and Over target.

Definition:

Measure > Target = Over Target

Measure < Target = Under Target

Measure = Target = On Target

What's the best solution to define "Under Target", "On Target" and "Over Target" based on the 3 columns in the script or in the expression where I need it?

In the script I already used the formular: Right(Measure,1) as MeasureNumber  to get number instead of tekst. So it is easier to compare.

.Please let me know what's the best solution script of expression and can you give me an example in QLV.

Thanks,

Ralph

[#2]

Hi Raplh

yes you can set your "Target label" into the script

it will the give you a field you could use a dimension to count your target according dimension

chris

[#3]

In your script:

TargetMeasure:

Load * inline

[Target

5];

You can change 5 to whatever number you need, then you can use the Target field in your expressions.

[#4]

Hello Jacob,

I think it's the right answer but I don't know how to do this in Qlikview!

I made this:

TagetMeasure:

Laod * Inline

[Target1,

Under Target,

On Target,

Over Target];

And now I have a field named Target1 with 3 items: Under target, On target and Over Target.

So far so good...

But how can I connect it?

[#5]

it should be:

TargetMeasure:

Load * Inline

[Target1, Under Target, On Target, Over Target,

1,2,3,4];

Where 1 is Target1, 2 is Under Target, 3 is On Target, and 4 is Over Target.

[#6]

Then in your expressions you can use:

count({Measure = ">=[Over Target]"}Gauges)

Where Measure is your measure field, Over Target is what we defined above, and Gauges is your gauges field.

[Anonymous · Accepted solution]

Hi Ralph

According to me, Script level is best for doing this comparison,once calculated we can directly pull the field from the table instead of writing an expression in the chart level.

I have attached the test .qvw for the same

[#8]

You can use the below expression:

if(Num(Right(Measure,1))> Num(Right(Target,1)), 'Over Target', if(Num(Right(Measure,1))< Num(Right(Target,1)),'under Target',if(Num(Right(Measure,1))= Num(Right(Target,1)),'On Target')))

Hope this helps.

[#9]

Thank you! 

It works

[#10]

No problem, Good luck!