Issue with the format of calculation

  • Hello friends,

    This issue is a little hard to explain, but I hope you will be able to understand what I am trying to ask. I believe my issue is that I format the formula in SQL incorrectly.

    I need to have such a calculation namely f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1 which will give me Sales vs Forecast. I need to have this in a %.

    How I typed in SQL

    FORMAT(f.TOTHIST / (f.TOTFCST - f.TOTHIST) - 1, 'P0') AS 'Sales VS FCST'

    This is what I get



    20 / (20.002 - 20) cannot be 999.900%. It is not right.

    Does someone have an idea what can it be?

    Please let me know.

    Thank you in advance.

  • What I am trying to show is that we sold 20 units. We forecasted we would sell 20.002. Total error between sold and forecasted is only 0.002. Sales VS FCST ratio should be extremely minor, like less than a %. I hope my explanation makes some sense. It is really hard to explain.

  • I believe the calc should be:

    CAST(((f.TOTFCST - f.TOTHIST) * 100.00) / f.TOTHIST AS decimal(5, 2))

    I have no idea at all why you are subtracting 1 from the quotient in your formula.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Hello Scott,

    I think you must be right. Thank you.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply