Quartile Function in Access

[tennisbuck]

Hi Board,

Really stumped and was hoping for some help. Most likely this has to be done in VBA and this is not my strong point in data analysis. I basically have a table in this format:

Sold To Marketing Name Total Sales Dollars
10009 Mufflers 100,000



My question is how would I create quartiles for each marketing name for the sold to's based on the sales dollars? Also are Fish Tail graphs a option in Access?



Thanks

Mike

Also Excel isnt a option since its 1m rows

[June7]

"Fish Tail", i.e. fish curve. Never seen this so went to Wiki. I suppose if you can calculate a dataset of x/y pairs, they could be plotted. Whether or not Access graphing engine could properly draw the line is another matter. I have doubts.

Need more help on what you mean by 'quartiles'.

[tennisbuck]

Thanks. I agree with the graph.

Quartile is 25% of items are in a range 50% 75% 100%. Here's what wikipedia says http://en.wikipedia.org/wiki/Quartile

Also heres some code I found, I just don't know how to layer in the market name.

This code was originally written by Michel Walsh.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code courtesy of
' Michel Walsh
'
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)


' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(Master, Test, _
"DCount(""*"", """ & Master & """, """ & Test & _
"<="" & [" & Test & " ]) >= " & _
X * DCount("*", Test))
End Function
' ***************Code start**************

[AlexHedley]

Cross Post:
http://www.access-programmers.co.uk/...d.php?t=258635

[June7]

What is it with percentiles this week!? The 3rd thread in 2 days referencing the same function. https://www.accessforums.net/queries...ile-40273.html

What are Master and Test? Why isn't the code using the declared variables?

[tennisbuck]

Hi June7,

Master is the table name and test is the field I want for quartiles.

[June7]

Look at the other thread. I posted a possible solution you should be able to adapt.

[tennisbuck]

Here's what the table looks like. So for the muffler Marketing name sold 1009 would be the first quartile and 1001 would be the second based on one year total sales.

Sold To	Material	Marketing Name	Sales November	Sales October	Sales September	Sales August	Sales July	Sales June	Sales May	Sales April	Sales March	Sales Feb	Sales Jan	Sales Dec	One Year Total	Quartile	Mean	Negative 3 Standard Deviation	Negative 2 Standard Deviation	Standard Deviation	2 Standard Deviation	3Standard Deviation	Trend
10009	12	Muffler	$ 100.00	$ 50.00	$ 18.00	$ 100.00	$ 1,000.00	$ 50.00	$ 100.00	$ 12.00	$ 500.00	$ 100.00	$ 100.00	$ 10.00	$ 2,140.00
10001	13	Muffler	$ 25.00	$ 30.00	$ 50.00	$ 230.00	$ 40.00	$ 20.00	$ 40.00	$ 25.00	$ 55.00	$ 100.00	$ 12.00	$ 100.00	$ 727.00
10009	14	Tire	$ 40.00	$ 43.00	$ 46.00	$ 49.00	$ 52.00	$ 55.00	$ 58.00	$ 61.00	$ 64.00	$ 67.00	$ 70.00	$ 73.00	$ 678.00
10001	15	Tire	$ 100.00	$ 102.00	$ 104.00	$ 106.00	$ 108.00	$ 110.00	$ 112.00	$ 114.00	$ 116.00	$ 118.00	$ 120.00	$ 122.00	$ 1,332.00
10009	16	Door	$ 500.00	$ 498.00	$ 496.00	$ 494.00	$ 492.00	$ 490.00	$ 488.00	$ 486.00	$ 484.00	$ 482.00	$ 480.00	$ 478.00	$ 5,868.00
10001	17	Door	$ 10.00	$ 11.00	$ 12.00	$ 13.00	$ 14.00	$ 15.00	$ 16.00	$ 17.00	$ 18.00	$ 19.00	$ 20.00	$ 21.00	$ 186.00

[June7]

What do you mean by 'first' and 'second' - that you want the value calculated for each MarketingName/SoldTo group?

I know very little about statistical analysis. What values should be in the Quartile field? Do you know the formula?

Your data is not a normalized structure. That will probably complicate any solution, as you discovered in other thread https://www.accessforums.net/access/...row-29040.html. If the data were normalized, VBA would not have been need for that issue. A query could have accomplished.

This data can be rearranged into a normalized structure with a UNION query. There is no wizard or designer for UNION, must type into SQL View of query builder. There is a limit of 50 SELECT lines.

SELECT Material, [Sold To], [Marketing Name], 11 AS SalesMonNum, "November" AS SalesMonName, [Sales November] AS Sales FROM tablename;
UNION SELECT Material, [Sold To], [Marketing Name], 10, "October", [Sales October] FROM tablename
...;

Now use that query as the data source for subsequent aggregate analyses, perhaps even with the function solution from the other thread referenced in post 5.

Otherwise, review http://support.microsoft.com/kb/209839


Why are the month fields arranged in that order? Is this database only one year's worth?

[tennisbuck]

Hi June7,

A quartile basically divides a group in this cast (Marketing Name) into 4 equal parts and will show which quartile is in the 1st, 2nd, 3rd, and fourth quartile. So lets say we had 100 records there would be roughly 25 sold to's in each quartile.

The months are arranged like that because thats the way they come out of our data warehouse.

For the Union query do I just copy and paste your code below and paste into the union option?

[June7]

That doesn't seem to be quite what the other poster wanted and sounds harder. They wanted percentile value. You want to assign a quartile group number to each record. What I am not clear about is what should constitute a 'record'. I see only 2 records for each MarketingName value. Would there be more for each in complete dataset? Do you want the quartile grouping to be by MarketingName/SoldTo/Month?

If you want to divide records into groups (either as they currently are or as normalized by UNION), review this info about ranking/numbering records: http://allenbrowne.com/ranking.html


Almost just copy/paste, I don't know your table name. Instead of the ellipsis (...) continue with the other months. Copy/paste and edit as needed.

[tennisbuck]

Awesome. I used the Access DB page and now I have the following results in my table.

I read through the rankings page and it was hard for me to follow. Do you have any suggestions?

[June7]

I am still not clear on what you want. You haven't explicitly answered my questions. Provide an example of the output you want to produce.

I don't see more than 3 records for each SoldTo.

[tennisbuck]

Sorry about not being very clear. Here's an example attached below. Let me know if I need to add anything else.

[June7]

I don't know the algorithm that the Excel Quartile function uses. How does it come up with 3225.24 for the 1st quartile for Doors? None of the data rows have that value and no sum of any combination of rows will return that value.

If you can provide a plain English description of the algorithm, might be able to translate to code. If you calculate the quartiles manually, what would be the exact steps?