08-03-2017 11:42 AM
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.
08-03-2017 12:37 PM
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.
08-03-2017 01:42 PM - edited 08-03-2017 01:43 PM
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.
08-17-2017 05:21 PM
Thank you! I haven't had a chance to see if this will work yet, but I believe it will do what I need. I appreciate the help.