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.
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.
Thank you MFGF.
It works.
Is there a way to compute the quartile when working with a dimensional source?
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.
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.
Quote from: MFGF on 22 Aug 2013 11:54:00 AM
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!