Post reply

Issue with the format of calculation

  • September 21, 2021 at 10:17 pm

    #3931635

    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

    Issue17

     

    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.

  • September 21, 2021 at 10:39 pm

    #3931641

    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.

  • September 21, 2021 at 10:43 pm

    #3931645

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • September 22, 2021 at 12:27 am

    #3931676

    Hello Scott,

    I think you must be right. Thank you.

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

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