topic Re: Ceiling function is not working for all values in QlikView

Ceiling function is not working for all values

Thu, 03 Apr 2014 17:27:25 GMT

Hi, someone created a calculated dimension for me to take our Style price and mark it up by 440% and round up to the nearest 5. For example, a style price is $68 and marked up it comes out to $299.20 and we want it rounded up to $300. This has been working for us, but I just discovered an issue with some prices. I have a style price of $125 that is calculating to $550 and that is what appears for the retail, which is correct. But I have a style price of $112.50 that should come out to $495, however this calculation is making it come out at $500. Why would it round up the number $495 to $500, but not round up $550 to $555? It seems to only be happening with this particular number. If the calculation result is ending with the number 5, then I need it to stay at that number and not round it up so that it ends in 0. Should I change the ceiling amount from 5 to something else?

if(StyleMetal<>'Silver',StylePrice*4,ceil((if(StyleMetal='Silver',StylePrice*4.4,(if(StyleMetal=StyleMetal,StylePrice*2)))),5))

I'd appreciate any assistance. Thank you!

Re: Ceiling function is not working for all values

swuehl — Thu, 03 Apr 2014 17:34:18 GMT

I don't understand the full logic of your expression, for example the condition StyleMetal = StyleMetal is always true, right?

If you could post a small sample application or sample data, this might help to understand.

Please note that rounding floating point values may show some issues:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors

Re: Re: Ceiling function is not working for all values

Thu, 03 Apr 2014 20:34:30 GMT

I'm not sure I understand the logic entirely either as someone else wrote this, but StyleMetal either equals Gold or Silver so I'm not sure why he used StyleMetal = StyleMetal. The logic should be if StyleMetal does not equal Silver, then take StylePrice * 4, otherwise if StyleMetal = Silver the StylePrice should be multiplied by 4.4 with a ceiling of 5. I attached some sample data from the object, so I hope this helps.

Thank you!

Re: Ceiling function is not working for all values

Thu, 03 Apr 2014 21:05:02 GMT

Try:

ceil(ceil(112.5*4.4,0.1),5)

It looks, like somewhere after '.' it is some value. But why????