topic expression effectively in QlikView

expression effectively

adiarnon — Tue, 23 Oct 2018 13:51:14 GMT

hi,

i have this expression-

if((sum(amount)-sum(budget))/number <10,'red',

if((sum(amount)-sum(budget))/number <30,'yellow',

if((sum(amount)-sum(budget))/number <50,'green',

if((sum(amount)-sum(budget))/number <70,'blue',

if((sum(amount)-sum(budget))/number <100,'purple')))))

The calculation time of the expression is very long and therefore I wanted to know whether it is possible to write the expression more effectively...

Because the expression (sum(amount)-sum(budget))/number) in each of the if conditions is the same

(something like Match in string)

can someone help?

adi

petter — Tue, 23 Oct 2018 14:10:04 GMT

You could always do:

=Pick(Floor(

((sum(amount)-sum(budget))/number)

/10)+1,'red','yellow','yellow','green','green','blue','blue','purple','purple','purple')

This doesn't account for negative values so it needs more fine-tuning I guess....

adiarnon — Thu, 25 Oct 2018 12:10:20 GMT

its not always interval of 10...

adiarnon — Thu, 25 Oct 2018 12:10:37 GMT

someone have an idea?