Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I was hoping to pick your brains to understand why the following code does not work
I have a column with multiple values which I would like to categorise into buckets according to value.
if(num(value) < num(0) and num(value) >= num(10), '<£10',
if(num(value) <= num(10) and num(value) >= num(50), '£10 - £50',
if(num(value) <= num(50) and num(value) >= num(100), '£50 - £100',
if(num(value) <= num(100) and num(value) >= num(499), '£100 - £499',
if(num(value) <= num(500) and num(value) >= num(999), '£500 - £999',
if(num(value) <= num(1000) and num(value) >= num(5000), '£1k - £5k',
if(num(value) <= num(5000) and num(value >= num(10000), '£5k - £10k',
if(num(value) <= num(10000) and num(value) >= num(50000), '£10k - £50k',
if(num(value) <= num(50000) and num(value) >= num(100000), '£50k - £100k',
if(num(value) <= num(100000) and num(value) >= num(500000), '£100k - £500k',
if(num(value) <= num(1000000) and num(value) >= num(10000000), '1M – 10M',
if(num(value) > num(10000000) and num(value >= num(900000000000000000000000000), '>10m')))))))))))) as [SumGroupings]
However the result is usually '>10m' even though the column value has a vast range of values from very small to very large figures.
Would anyone happen to know why this is happening?
Thanks,
Isaac
And you might be able to do away with num() functions all together
if(value > 0 and value <= 10, '<£10',
if(value > 10 and value <= 50, '£10 - £50',
if(value > 50 and value <= 100, '£50 - £100',
if(value > 100 and value <= 499, '£100 - £499',
if(value > 500 and value <= 999, '£500 - £999',
if(value > 1000 and value <= 5000, '£1k - £5k',
if(value > 5000 and value <= 10000, '£5k - £10k',
if(value > 10000 and value <= 50000, '£10k - £50k',
if(value > 50000 and value <= 100000, '£50k - £100k',
if(value > 100000 and value <= 500000, '£100k - £500k',
if(value > 1000000 and value <= 10000000, '1M – 10M',
if(value > 10000000 and value <= 900000000000000000000000000, '>10m')))))))))))) as [SumGroupings]
I think you < and >= signs are incorrect... try this
if(num(value) > num(0) and num(value) <= num(10), '<£10',
if(num(value) > num(10) and num(value) <= num(50), '£10 - £50',
if(num(value) > num(50) and num(value) <= num(100), '£50 - £100',
if(num(value) > num(100) and num(value) <= num(499), '£100 - £499',
if(num(value) > num(500) and num(value) <= num(999), '£500 - £999',
if(num(value) > num(1000) and num(value) <= num(5000), '£1k - £5k',
if(num(value) > num(5000) and num(value) <= num(10000), '£5k - £10k',
if(num(value) > num(10000) and num(value) <= num(50000), '£10k - £50k',
if(num(value) > num(50000) and num(value) <= num(100000), '£50k - £100k',
if(num(value) > num(100000) and num(value) <= num(500000), '£100k - £500k',
if(num(value) > num(1000000) and num(value) <= num(10000000), '1M – 10M',
if(num(value) > num(10000000) and num(value) <= num(900000000000000000000000000), '>10m')))))))))))) as [SumGroupings]
And you might be able to do away with num() functions all together
if(value > 0 and value <= 10, '<£10',
if(value > 10 and value <= 50, '£10 - £50',
if(value > 50 and value <= 100, '£50 - £100',
if(value > 100 and value <= 499, '£100 - £499',
if(value > 500 and value <= 999, '£500 - £999',
if(value > 1000 and value <= 5000, '£1k - £5k',
if(value > 5000 and value <= 10000, '£5k - £10k',
if(value > 10000 and value <= 50000, '£10k - £50k',
if(value > 50000 and value <= 100000, '£50k - £100k',
if(value > 100000 and value <= 500000, '£100k - £500k',
if(value > 1000000 and value <= 10000000, '1M – 10M',
if(value > 10000000 and value <= 900000000000000000000000000, '>10m')))))))))))) as [SumGroupings]
Another and even easier and more performant approach would be to use a pick(match()) for it like:
= pick(match(-1, value < 10, value < 50, ...), '<10', '<50', ...)
- Marcus
ps: the origin idea to compare conditions against TRUE is from Sunny.
You're right, it was my greater than smaller than logic that was incorrect. Thank you for your help, wish I asked this earlier instead of spending days trying to figure out why its not working