topic Re: How to limit a value at 100% in QlikView

How to limit a value at 100%

Wed, 29 Jan 2014 12:00:59 GMT

Hi all,

I have the following expression

(count({$<Origin={PLANNED,BOTH},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

+count({$<Origin={ACTUAL},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

-count({$<Origin={PLANNED},Type={ARRIVEE},Status={CANCELLED}>} distinct DateID&TrainID&Station&Planned)

//SIG1 = O&M1 +ADD-CAN

-count({$<Type={DEPART},Status={EARLY}>}distinct DateID&TrainID&Station&Planned)//SIG2 = EARLY

)

/( //O&M1 = PLANNED

count({$<Origin={PLANNED,BOTH},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

)

In some cases the expression will return a value that is greater then 100%

Any idea on how to limit it so that if the value returned is > 100% it'll just show 100%

Re: How to limit a value at 100%

Wed, 29 Jan 2014 12:04:43 GMT

Check the relative option in expression tab

Re: How to limit a value at 100%

sujeetsingh — Wed, 29 Jan 2014 12:07:11 GMT

If it is bar graph then set it at Properties>>Axex>>

on Scale set max to 1.0

Re: How to limit a value at 100%

Wed, 29 Jan 2014 12:12:50 GMT

Thanks for the replies.

Unfortunately the relative option impacts other values and changes everything

And as for the bar graph suggestion - it's a simple table with values

Re: How to limit a value at 100%

Wed, 29 Jan 2014 12:14:27 GMT

Can you attach ur sample

Re: How to limit a value at 100%

Wed, 29 Jan 2014 12:17:25 GMT

Thanks for the reply again I've attached a picture of it above.. Can't attach the qlikview file as it's too big.

Re: How to limit a value at 100%

Wed, 29 Jan 2014 13:04:18 GMT

You can use rangemin()

Rangemax(1, 2, 3) will return 3

Rangemin(1, 2, 3) will return 1

Rangemin(Your Expression, 100%) will not return a value > 100%

Fabrice

Re: How to limit a value at 100%

Wed, 29 Jan 2014 13:11:00 GMT

Thanks Fabrice

Could I ask that you put it in the example given above? I'm not too sure where the Rangemin() would fall in

(count({$<Origin={PLANNED,BOTH},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

+count({$<Origin={ACTUAL},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

-count({$<Origin={PLANNED},Type={ARRIVEE},Status={CANCELLED}>} distinct DateID&TrainID&Station&Planned)

//SIG1 = O&M1 +ADD-CAN

-count({$<Type={DEPART},Status={EARLY}>}distinct DateID&TrainID&Station&Planned)//SIG2 = EARLY

)

/( //O&M1 = PLANNED

count({$<Origin={PLANNED,BOTH},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

)

I tried rangemin(

(count({$<Origin={PLANNED,BOTH},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

+count({$<Origin={ACTUAL},Type={ARRIVEE}>} distinct DateID&TrainID&Station&Planned)

-count({$<Origin={PLANNED},Type={ARRIVEE},Status={CANCELLED}>} distinct DateID&TrainID&Station&Planned)

//SIG1 = O&M1 +ADD-CAN

-count({$<Type={DEPART},Status={EARLY}>}distinct DateID&TrainID&Station&Planned)//SIG2 = EARLY

) , 100% )

However that obviously doesn't work.

Re: How to limit a value at 100%

Wed, 29 Jan 2014 13:18:52 GMT

Sean,

i am not a compilator.

But yes, Rangemin() should include your expression that returns 128% (and that you want to minimize to 100%)

Rangemin(your expression that returns the percentage, 100%)

Perhaps, you have a problem with all the parenthesis, the comment you have put or ...

Fabrice