3 weeks ago
I have a table of data and need to use one calculation for one region and a different calculation for the other regions. The calculations would include other columns of data. Is it possible to combine functions into one calculated column.
Data includes Region and Current Balance.
This is what I am trying to accomplish:
If the Region = X and the Current Balance is <100,000 then the end result is a flat $200. For Region X, if the Current Balance is >100,000 the end result is Current Balance*.0025. For all other Regions if the Current Balance is <100,000 then the end result is a flat $500 or if the Current Balance is >100,000 then the end result is Current Balance*.005.
How do I accomplish this? I am not a programmer and need to be able to do this within the Query Builder. Thank you.
3 weeks ago
You'd have to do it using the advanced expression option. It kind of walks you thru it a bit, in that the functions are all listed as well as their parameters.
3 weeks ago - last edited 3 weeks ago
In your case, you'd want the CASE statement in your Advanced expression. Something like:
case when region="X" and balance<100000 then 200 when region="X" and balance>=100000 then balance*.0025 when region ^="X" and balance<100000 then 500 else balance*.005 end /* as NewValue */
The expression builder will add the "as NewValue" bit on for you, but you'll have to pretty much enter the rest. You can try the Recode Values option instead -- it might give you some point and click methods for this. The end result would still be a CASE clause.