# Calculated Fields

Maria A. Maldonado shared this question ago

I'm having a problem using a field(number) in the calculated field can someone help me?

Values:

Target = 400,000.00

Third-range-amt = 1.10

Third-range-perc= 0.06

Second-range-amt = 1.0

second-range-perc= 0.05

First-range-amt = .90

first-range-perc= 0.04

SQL Case Statement:

CASE WHEN SUM (Total Sales ) > ( Target * Third-Range-Amt ) THEN SUM ( Total Sales ) * ( 1 + Third-Range-Perc ) WHEN SUM (Total Sales ) BETWEEN ( Target * Second-Range-Amt ) AND ( Target * Third-Range-Amt ) THEN SUM ( Total Sales ) * ( 1 + second-Range-Perc ) WHEN SUM (Total Sales ) BETWEEN ( Target * First-Range-Amt ) AND ( Target * Second-Range-Amt ) THEN SUM ( Total Sales ) * ( 1 + First-Range-Perc ) ELSE SUM ( Total Sales ) END

## Replies (3) 1

Hi Simon,

The following SQL code is working:

CASE WHEN SUM (Total Sales ) > ( Target * 1.1 ) THEN SUM ( Total Sales ) * 1.06 WHEN SUM ( Total Sales ) BETWEEN Target AND ( Target * 1.09 ) THEN SUM ( Total Sales * 1.05 ) WHEN SUM ( Total Sales ) BETWEEN ( Target * 0.9 ) AND ( Target * 0.99 ) THEN SUM ( Total Sales ) * 1.04 ELSE SUM ( Total Sales ) END

But, I want to have a variable for each number as I had describe in the reference ¨values¨ for the problem before.

So, I just replace the number values for the variables with the same number value in each field.

I do not see what is the problem in the code when is just a replacement of variables.

Why with numbers the SQL code works and with variables it doesn't??

Thanks Again, 1

Hi Simon,

Sorry for the late response,

I've been testing data to make sure I can use these parameters you taught me. I think that these parameters will be very useful. Therefore, as I have not achieved my goal I would like to leave the dialogue open just in case I need more help. But thank you very much for everything.

Grateful,

Maria A. 1

Hi Simon,

Thank you so much for everything it was very helpful.

I used the parameters to achieve the expected result and it works.

Thanks Again,

Maria