Post reply

PERCENT_RANK()

  • June 19, 2015 at 7:25 am

    #319178

    Hello,

    I have a formula I am having difficultly with and cannot seem to find a solution for it. My understanding of the PERCENT_RANK function is it will break up your partition bebetween and 100% in float/decimal format. The trouble I am having is with this formula, I am getting percentages between 0 and 0.995652174 and I am not sure why it wont go to 100%.

    Here is my current formula:

    ,PERCENT_RANK() over (partition by PARTITON ORDER BY (sum(CASE WHEN cast((Field1)as float)= 0 THEN 0 ELSE (cast((Field2) as float) / cast((Field1) as float)) - (cast((Field3) as float)/cast((Field1) as float)) END)) ASC) AS Column_Label

    Can someone assist?

    Thanks

  • June 22, 2015 at 10:15 am

    #1807101

    I think this might be because the floating data type only stores an "approximate" number and not an exact one.

    So you may get close to 100% but never exactly there.

  • June 22, 2015 at 10:46 am

    #1807107

    I know floating is used for the data type for percent_rank(). I tried changing to something in a previous post but that didn't change the output at all.

  • June 23, 2015 at 7:14 am

    #1807341

    CPiacente (6/22/2015)

    I know floating is used for the data type for percent_rank(). I tried changing to something in a previous post but that didn't change the output at all.

    If you recall, using PERCENT_RANK() with a small number of records is less likely to produce useful results. If the number of output records in the result set is fairly small - say 3 to 5, then the numbers you get from this function aren't likely to be all that useful, as it seems likely a person could do a pretty simple mental estimate of the result just by looking at the source numbers. Getting an exact 100% is not necessarily likely for ANY number of source records, and is not guaranteed. For small numbers of records, rounding might be the best solution, even there, ties for the top spot can keep the highest value well away from 100%.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

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

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