We help IT Professionals succeed at work.

# How to create Quintiles

on
2,044 Views
My user wants me to create a report that lists various income statement averages for a given shop

They also want to add a colum in the report that lists the average of the income statement averages for all shops that are in the same sales quintile.

I'm thinking I need a way to indicate which sales (4499_TotalRev)  quintile each of the shops is in so I can retrieve the shops in the correct quintile.  (Perhaps add a field called quintile and somehow update it)

here is a small sample of the data in the table named incomeStatement
9901_Shop            4499_TotalRev    4999_TotalGrossMargin    5200_GrossMarginNetPayroll
4166            362872            34            22
1110            898000            12            33
1115            645673            56            44
1117            1050815            12            10
1127            591677            88            15

I'm thinking I have to determine what the quintiles are and then find a way to indicate which shop is in which quintile but not sure the best way to do this.

Any help would be greatly appreciated
Comment
Watch Question

## View Solution Only

Commented:
Yep, you're exactly. right - the record needs to know what quintile its in before you can find out what percentage it is.

What options fdo you have? I take it you can add fields.

How often is the report run? hourly/monthly?

Will the quintile always be evaluated over the same period, or does this table not contain periods? Is this the shop master table?

A long term enterpise solution is to create a data warehouse.

A short term solution is to create a quintile field (as you surmised) and populate it.

If the guy is talking about quintiles then he'll probably want a rank too at some stage. So I suggest you create a rank field and populate it. Then use the Rank field to evaluate the quintile.

To populate a rank field called ShopRank in this table, assuming that 9901_Shop is a unique field indicating the shop, use the following:

CREATE TABLE #ShopRank (9901_Shop INT, 4499_TotalRev DECIMAL(19,2), ShopRank INT Identity (1,1))

INSERT INTO #ShopRank (9901_Shop, 4499_TotalRev)
SELECT 9901_Shop, 4499_TotalRev
FROM incomeStatement
ORDER BY 4499_TotalRev DESC

UPDATE incomeStatement
SET ShopRank = #ShopRank .ShopRank
FROM #ShopRank
WHERE  incomeStatement.9901_Shop = #ShopRank.9901_Shop

DROP TABLE #ShopRank

Then you can simply assign the quintile (or any kind of ranked banding) as follows:

UPDATE incomeStatement
SET QuintileMember = (ShopRank + 1) / (SELECT MAX(ShopRank) / 5 FROM incomeStatement)

Then you can assign the quintile total (the total for each quintile, assigned to each record) like this:

UPDATE incomeStatement
SET QuintileTotal = QuintileRevenue
FROM
(
SELECT QuintileMember, SUM(4499_TotalRev) As QuintileRevenue
FROM incomeStatement
GROUP BY QuintileMember
) A
WHERE incomeStatement.QuintileMember = A.QuintileMember

Then you can assign the quintile percentage like this:

UPDATE incomeStatement
SET QuintilePercentage = QuintileRevenue / 4499_TotalRev * 100

Note that this requires the addition of these fields:

ShopRank (INT)
QuintileMember (TINYINT)
QuintileTotal (DECIMAL(19,2)
QuintilePercentage (DECIMAL(9,2)

Commented:
nmcdermaid,

Thanks so much for the detailed reply!

You raise some good questions/points that I didn't address in my original post.

I do have the option to add fields.

The report is run on demand. (potentially the same shop for different reporting periods)

The quintile will be evaluated over multiple periods as the table does indeed not contain periods.

I realize there is a bit more complexity as this table will contain multiple income statemens for the same shop for different reporting periods.

Commented:
I accidently posted my response before I realized that I wrote

The quintile will be evaluated over multiple periods as the table does indeed not contain periods.

when I meant

The quintile will be evaluated over multiple periods as the table does indeed contain periods.

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)

Commented:
the table could contain many records for a shop.

We have a web form that allows shops to enter income statement values for a given period.

They can do it on an annual basis or for a given month.  In either case they can not do data entry for an overlapping period.  For example,  if an income statement was entered for the period of january 2006 and december 2006, another entry can not be made for this same period or for any period that would overlap this period.  For example may 2006 to april 2007.

The shops have an option to see a report from the data entered for any of the periods for which they have entered data.  The report slices and dices data in different ways.  The quintiles would be for the periods entered

Unlock the solution to this question.