How to limit a value at 100%

[#1]

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%

??

[#2]

Check the relative option in expression tab

[sujeetsingh]

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

on Scale set max to 1.0

[#4]

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

[#5]

Can you attach ur sample

[#6]

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

[#7]

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

[#8]

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.

[#9]

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