Hi - have a dashboard in custom mode with a number of calculated fields.

Stock committment rate % (calculated)

Stock committment target % (from upload)

Stock committment variance to target %(calculated)
I’m trying to add some logic in that says…
If stock committment variance to target is greater then 10%, then have another column that returns “Discount by 5%” or even “5”
This repeated… so if stock committment variance to target is greater then 20%, then returns “discount by 10%” or even “10”
I can’t work out the sql (if that’s the best way), or alternative ways to show the extra column… any help or suggestions very much appreciated…

Thanks so much Jon… That is exactly the concept that I’m looking for. So glad that it is possible!
My variance formula is slightly more detailed - so think this concept should work. Think I might have a bracket placement error though as get a message “an error has occurred when querying the data”. My variance custom formula ie. a-b equivalent is: ((a/(a+b+c+d))*100)-(e/10)
where
a = ((a/(a+b+c+d))*100); and
b = (e/10)

Then my “Funky Answer” uses that same formula with the braces
^case WHEN ( {((a/(a+b+c+d))*100)}-{(e/10)} )< 0 THEN 10 ELSE 0 END
I’ve tried to replicate the spacing of your brackets to be exact in case that’s the issue.
Thanks so much for your help. Best Regards, Garney

Hi - have spent ages today trying to work this out, including discussing with some SQL developers…
it definitely works (thanks Jon), for two variables as per Jon’s example.
Adding more variables, even with the pass through SQL being reviewed by developers, it doesn’t seem to work.
my formula - trying to replicate Jon’s structure:
^case WHEN ( {((a/(a+b+c+d))*100)}-{(e/10)} )< 0 THEN 10 ELSE 0 END
formula from developer that was tested in parts (and worked), just didn’t work putting it together
^case WHEN (( ((a/(a+b+c+d))*100)-(e/10) )) < 0 THEN 10 ELSE 0 END
any assistance greatly appreciated or maybe there are limitations with how Phocas can handle these statements. thanks very much

@Garney I think your just missing the point behind the curly brackets. “{ }” When you’re using SQL commands rather than a straight up calculation, you need to enclose each variable name in the curly brackets. So instead of just “a”, it’s “{a}”, instead of “b”, use “{b}”, and instead of “c” use “{c}”, etc.