Calculated Fields

Maria Maldonado shared this question 6 months 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

Best Answer
photo

Hi Maria,


I understand what you are trying to achieve now. What I would suggest using is 'Parameters' rather than 'Field' values, and use the Parameters as variables in the Calculated Field you are creating.


Read this article and let me know how you go.


Kind regards,Simon

Comments (7)

photo
1

Hi Maria,


Thank you for your question.


Could you explain exactly what you are having trouble with? What are you aiming to do with this calculated field?


On initial inspection, it seems that you aggregating values incorrectly - i.e. "SUM(Total Sales)" will give you the aggregated total of the Total Sales column, rather than the value of the individual field within that column, row by row, which is what I am guessing you are aiming for. This means that the first argument will always be the same number, meaning that the CASE statement will always be true for the first value "SUM ( Total Sales ) * ( 1 + Third-Range-Perc )", or the value in the ELSE statement "SUM ( Total Sales )".


If that's the case, I would remove all mentions of "SUM" and see if this helps with your query. Otherwise, I'm going to need a little more information about what you are aiming to achieve with this statement!


Kind regards,

Simon

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
2

Hi Maria,


I understand what you are trying to achieve now. What I would suggest using is 'Parameters' rather than 'Field' values, and use the Parameters as variables in the Calculated Field you are creating.


Read this article and let me know how you go.


Kind regards,Simon

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


Not a problem, I will leave this particular question open for a little while longer.


Please note that when a ticket is marked as completed, you are still able to comment on the question, if you still have any further issues on the topic.


Thanks,

Simon

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

photo
1

Hi Maria,


Not a problem, I'm glad I could help!


I'll go ahead and mark this question as completed.


Kind regards,

Simon