Calculating Quartiles

[Mark]

Hello,

Is there a way of calculating quartiles (value of a field
at each 25% of observations when sorted into ascending
order) in a query expression?

Excel can do this with the "Quartile" function but Access
does not seem to support this.

At the moment I am hard coding the values of the quartiles
into my query expression like this:

Quartile: IIf([tblStore].[SumOfValue]<=438771,"Low",IIf
([tblStore].[SumOfValue]<=1645333,"Medium",IIf([tblStore].
[SumOfValue]<=4840280,"High",IIf([tblStore].[SumOfValue]
<=23730230,"Very High","Error"))))

The 4 numbers in this expression are the values of the
quartiles as calculated in Excel, but I need a more
dynamic way of doing it so that if the underlying data
changes so will my quartile values.

Thank you for any help in advance.

Mark

 

[Michel Walsh]

Hi,




SELECT a.value, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.value <= b.value
GROUP BY a.value

would rank the values (First, second, 3, 4, ....., N ). If you save that
query, say under the name Q1, then



SELECT INT( (q1.rank-1)/4 )+1 As Quartile,
MIN(q1.value) As lowestValueInQuartile,
MAX(q1.value) As highestValueInQuartile
FROM q1
GROUP BY INT( (q1.rank-1)/4 )+1


that would return the minimum and maximum value for each quartile



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


replace

INT((q1.rank-1)/4)+1

with


INT(4*(q1.rank-1)/(SELECT MAX(q1.rank)))+1



at both two occurrences.


The idea is to produce a value giving the quartile (1, 2, 3 or 4) the
rank makes the values to belong to.



Hoping it may help,
Vanderghast, Access MVP

Hi,




SELECT a.value, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.value <= b.value
GROUP BY a.value

would rank the values (First, second, 3, 4, ....., N ). If you save that
query, say under the name Q1, then



SELECT INT( (q1.rank-1)/4 )+1 As Quartile,
MIN(q1.value) As lowestValueInQuartile,
MAX(q1.value) As highestValueInQuartile
FROM q1
GROUP BY INT( (q1.rank-1)/4 )+1


that would return the minimum and maximum value for each quartile



Hoping it may help,
Vanderghast, Access MVP

Hello,

Is there a way of calculating quartiles (value of a field
at each 25% of observations when sorted into ascending
order) in a query expression?

Excel can do this with the "Quartile" function but Access
does not seem to support this.

At the moment I am hard coding the values of the quartiles
into my query expression like this:

Quartile: IIf([tblStore].[SumOfValue]<=438771,"Low",IIf
([tblStore].[SumOfValue]<=1645333,"Medium",IIf([tblStore].
[SumOfValue]<=4840280,"High",IIf([tblStore].[SumOfValue]
<=23730230,"Very High","Error"))))

The 4 numbers in this expression are the values of the
quartiles as calculated in Excel, but I need a more
dynamic way of doing it so that if the underlying data
changes so will my quartile values.

Thank you for any help in advance.

Mark