Calculated Fields

Maria A. Maldonado shared this question 3 years ago
Answered

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)

photo
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,

Maria Maldonado Laboy

photo
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.

photo
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

Leave a Comment
 
Attach a file