topic Re: Compare two ranges in QlikView

Compare two ranges

Fri, 06 Jun 2014 13:28:47 GMT

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

Re: Compare two ranges

Fri, 06 Jun 2014 14:01:01 GMT

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

Re: Compare two ranges

Fri, 06 Jun 2014 14:22:53 GMT

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.

Re: Compare two ranges

Fri, 06 Jun 2014 14:36:54 GMT

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?

Re: Compare two ranges

Fri, 06 Jun 2014 14:49:38 GMT

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.

Re: Compare two ranges

Fri, 06 Jun 2014 14:51:45 GMT

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.

Re: Compare two ranges

Anonymous — Fri, 06 Jun 2014 14:52:14 GMT

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

Re: Compare two ranges

Fri, 06 Jun 2014 14:53:00 GMT

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.

Re: Compare two ranges

Fri, 06 Jun 2014 19:33:56 GMT

Thank you!

It works

Re: Compare two ranges

Fri, 06 Jun 2014 19:49:22 GMT

No problem, Good luck!