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…
Hi @Garney,
If I understand correctly, you want a column to report a specific value when the calculation value = x, but the knack is it needs to be dynamic based on user selection?
If so you will need to have an additional custom column with a case statement in it, which inspects your values. This can be achieved as follows:
Example of Current Order Qty - Previous Order Qty = Variance
So far so, good, now we want to do this:
If variance > 10,000 then 5 else 0
Go back into your custom mode and do the following:
The import part is where you normally do Measure A - Measure B, this needs to be a case statement. The syntax of this is: ^case WHEN ( {a}-{b} )> 10000 THEN 5 ELSE 0 END
The end result is this:
Things to note:
Case statement is limited to I think 6 where clauses, so if you can try to do as much as you can in the SQL stream first.
Using case statements in this way requires “{}” (braces) around each variable i.e. {a} - {b}
Pretty nifty element that I think @IT_Support should write a detailed article on? As I am sure there are some weird and wacky things we can do with this useful addition.
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.
