How to rank the Sales and divide them into quarters for each store size?

[lookingforK]

Hi,

I am using Report Studio 8.4 to extract data.

I want to obtain the 2012 information of the following fields:
• [Store ID]
• [Store Size]
• [Sales 2012]
• [Rank] ( ~ this data item should be created)
• [Quartile Indicator] (~ this data item should be created. Its valule could be: 1, 2, 3, or 4.)

The stores are classified by Size as A, B, and C (A > B > C).

The data should be sorted by Sales for each store size (and then be assigned with Quartile Indicator).

Please note:
* Here, "the quartiles of a set of values are the three points that divide the data set into four equal groups".

For example,
[Store Number]   [Store Size Code]   [Sales]   [Rank]    [Quartile Indicator]
116   A   57155   1   1
109   A   57202   2   1
114   A   57960   3   1
111   A   67199   4   1
118   A   67199   4   1
107   A   67914   6   2
106   A   67917   7   2
104   A   67932   8   2
117   A   67955   9   2
113   A   77199   10   3
115   A   77199   10   3
101   A   77920   12   3
112   A   87922   13   3
102   A   97955   14   3
103   A   233211   15   4
108   A   236709   16   4
110   A   332312   17   4
100   A   670689   18   4
105   A   745423   19   4
132   B   1670   1   1
126   B   37199   2   1
131   B   37199   3   1
120   B   37201   4   1
129   B   37947   5   2
125   B   37958   6   2
123   B   37964   7   2
130   B   47932   8   3
124   B   57201   9   3
127   B   57936   10   3
128   B   67932   11   4
122   B   137199   12   4
121   B   237936   13   4
119   B   337967   14   4
138   C   17958   1   1
141   C   27960   2   1
134   C   29679   3   1
133   C   37196   4   1
148   C   37196   5   1
139   C   37199   6   2
143   C   37199   7   2
145   C   37201   8   2
150   C   37201   9   2
144   C   37205   10   2
136   C   37922   11   3
142   C   37958   12   3
140   C   37960   13   3
147   C   37978   14   3
149   C   47924   15   4
135   C   57958   16   4
146   C   67201   17   4
137   C   77960   18   4


My questions are:
1) How to rank the Sales and divide them into quartiles for each store size?
2) How to write Data Item Expression for Quartile Indicator whose value could be 1, 2, 3, or 4?

Thank you in advance.

[MFGF]

Hi,

You will need two query calculations - one for the rank and one for the quartile.

Your rank calculation will be:

rank([Sales 2012] ASC for [Store Size])

Notice I used the ASC operator - normally the rank function will default to ranking in descending order (ie the highest value will be 1). Your sample below seems to indicate you want the lowest value in each store size to be rank 1.

Your quartile calculation will be:

5 - quartile([Sales 2012] for [Store Size])

Quartile always defaults to the highest values being in quartile 1 and the lowest in quartile 4. There is no ASC option with this function, so I have subtracted the result from 5 to give a reverse quartile - again what you appear to need.

Finally, you will need to fix the sort order. Group your report on Store Size, then sort on Rank Ascending.

Cheers!

MF.

[lookingforK]

Thank you MFGF.

It works.

[Lynn]

Is there a way to compute the quartile when working with a dimensional source?

[Lynn]

Nevermind...I was able to use the proper "within set" reference in the quartile function. I should have tried a bit more before posting the question.

[MFGF]

In the same vein as Grim being known as "Honorary master of IBM links", I vote you shall henceforth be dubbed "Quartile Queen of the Cognoise Kingdom" ;)

MF.

[Lynn]

 

In the same vein as Grim being known as "Honorary master of IBM links", I vote you shall henceforth be dubbed "Quartile Queen of the Cognoise Kingdom" ;)

MF.

;D
Too funny!