Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Unanswered: Quartile and Percentile Calculations

    Does anyone know of a way that I can do this for data held in Access XP. (Excel uses @quartile and @percentile.)

    Tx in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    This is how I do it. Basicaly you will need two queries if you need to create an other catagorie, wich i would so that you have a complete picture.

    The first query gets the object (ie country) and the sum of the measuers (ie revenue) and then the precentage that that country had of the total using a formula similar to :
    (Sum(Table_PCRKMS_Local_Data.FFE)/DLookup('sum(ffe)','Table_PCRKMS_Local_Data'))*100

    The formula basicaly says total revenue for that country devided by the total revenue of all records multiplied by 100. This will give you the perectage of that single record

    to get the other grouping use the second query:

    Select iif([Percent] > 1.5, [Port_Pairs],'OTHER*'),sum([FFE])
    from graph_FFE
    group by iif([Percent] > 1.5, [Port_Pairs],'OTHER*')
    order by 2 desc

    This basicaly says if the perecentage is greater than 1.5 then use the country else other and them sum the revenue. So with two queries you can have your percentages

    Regards
    Jim


    Dim Cur_DB As DAO.Database
    Dim Record_Set As DAO.QueryDef
    Set Cur_DB = CurrentDb()

    ' delete qrydef if it exists
    For Each qdf In Cur_DB.QueryDefs
    If qdf.Name = "graph_FFE" Then
    Cur_DB.QueryDefs.Delete "graph_FFE"
    Exit For
    End If
    Next qdf

    Country_To_Country.RowSourceType = "Table/Query"
    Set Record_Set = Cur_DB.CreateQueryDef("graph_FFE", "SELECT [Load] & ' - ' & [Discharge] AS port_Pairs" & _
    ", Sum(Table_PCRKMS_Local_Data.FFE) AS FFE ," & _
    " (Sum(Table_PCRKMS_Local_Data.FFE)/DLookup('FFE','Table:_PCRKMS_METRICS_SUM'))*100 as [Percent]" & _
    " FROM Table_PCRKMS_Local_Data" & _
    " GROUP BY [Load] & ' - ' & [Discharge]" & _
    " ORDER BY Sum(Table_PCRKMS_Local_Data.FFE) DESC;")
    Country_To_Country.RowSource = "Select iif([Percent] > 1.5, [Port_Pairs],'OTHER*'),sum([FFE]) from graph_FFE group by iif([Percent] > 1.5, [Port_Pairs],'OTHER*') order by 2 desc;"

  3. #3
    Join Date
    Jan 2003
    Posts
    4
    Tx Jim for the response.

    I think you have given me code to calculate percentage, rather than percentile. (The latter is the percentage position up a rank order of values - so 50th percentile is half-way up.)

    Regards,

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by robin.jordan
    Tx Jim for the response.

    I think you have given me code to calculate percentage, rather than percentile. (The latter is the percentage position up a rank order of values - so 50th percentile is half-way up.)

    Regards,
    ya your right sorry about that, but maybe you can use it as a modle, seems to me you will have to do two passes, first to rank and second to filter out the part that you want, maybe you can gather your metrics in the first pass along with some type of auto number sorting the query descending by the metric and then take the second pass to get the top 50 auto numbers......maybe........just a guess at this point
    Jim

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  6. #6
    Join Date
    Jan 2003
    Posts
    4
    Thanks Rudi, that got me started. A search on that website (which I had not come across before) actually gave me another link to some actual Access code to calculate quartiles. Problem solved.

    Thanks to all

  7. #7
    Join Date
    Jan 2003
    Posts
    4
    Jim,

    Your original code set me thinking if I ordered the values, calculated a value of their position and then compared it to the percentile value I'd be along the right lines (except I would not know how to code it). Luckily Rudi gave me a url to get me started - see other message). On that site I also found a link to

    http://www.fabalou.com/Access/Modules/percentile.htm

    This has some actual Access code that I can cut and paste in.

    Regards

  8. #8
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by robin.jordan
    Jim,

    Your original code set me thinking if I ordered the values, calculated a value of their position and then compared it to the percentile value I'd be along the right lines (except I would not know how to code it). Luckily Rudi gave me a url to get me started - see other message). On that site I also found a link to

    http://www.fabalou.com/Access/Modules/percentile.htm

    This has some actual Access code that I can cut and paste in.

    Regards
    Cool...maybe if we all start to think we will be able to come up with a solution...
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •