This is actually coming from a business place, but it's all about being able to do math tricks, and that's why I'm posting here.

I want to implement the following function:

1, x ∈ (-∞,0]
2, x ∈ (0,1500]  
3, x ∈ (1500,5000]  
4, x ∈ (5000,10000]  
5, x ∈ (10000,+∞)  

The problem is I am using a specific language (QlikView/QlikSense), which does not have any case-like statements. Currently I am using the all-too-common nested-if solution:

if(x>10000,5,(if x>5000,4,.......))

It works. The problem is that x is a really long and really heavy business calculation formula. Repeating it multiple times not only makes the formula a headache, but also makes me paranoid about the language itself executing the same calculation multiple times.

That's why I turned to this impressively creative community. Can you find a way, using common math functions, to create this formula without using x more than once?

The language has most math funcitons implemented. There are a few of its own as well, but I doubt they are going to help (There is already a range-splitting one, but it won't work with non-symmetrical ranges like here). Nothing to stop you from researching though.

  • $\begingroup$ I'm a little unsure about the restrictions. Are we disallowed from using, say, a polynomial, where $x$ appears more than once? Or could we define a function which maps to a polynomial expression and then use that function. $\endgroup$
    – hexomino
    Commented Aug 21, 2019 at 11:31
  • $\begingroup$ Surely, I'd like to avoid x more than once, else I'd have gone on the polynomial way. Creating an auxiliary function would still use x multiple times, so I guess that's out of spec too. $\endgroup$ Commented Aug 21, 2019 at 11:36
  • $\begingroup$ is the use of an array of lenght, lets say ... 22 allowed ? $\endgroup$
    – Neil
    Commented Aug 21, 2019 at 11:56
  • $\begingroup$ @hexomino you see hex, trying to find the answer to this question is especially difficult. There is a large amount of hearsay and superstitions about how the tool works and access to the true engineers seems impossible, at least to my moderate experience. I'm talking about the engine implementation - on the user level (which I am) no, there is not. $\endgroup$ Commented Aug 21, 2019 at 12:21
  • $\begingroup$ Does the language have an iferror function where it calculates something and returns it unless it's an error in which case it returns something else $\endgroup$
    – Dr Xorile
    Commented Aug 21, 2019 at 12:33

1 Answer 1


Not sure about the possibility of a dictionnary, but maybe this can do what you want : divide X by 500 and cap the result between 0 and 21, then use an array

dict = [1,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,5];

the part with the array is kinda bad because it means a very big array depending on the unequal ranges, but in your specific case it seems acceptable if we cant find a function that changes exactly on 0,3,10 and 20 and use only one x

  • 1
    $\begingroup$ So simple! Thanks! I might later post the real code just for the sake of it. $\endgroup$ Commented Aug 21, 2019 at 12:23
  • $\begingroup$ As promised: pick(1+rangemin(21,rangemax(0,ceil(x/(500-0.00001)))),1,2,2,2,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,5)-1)). Thanks again! $\endgroup$ Commented Aug 21, 2019 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.