Topic
  • 2 replies
  • Latest Post - ‏2013-01-16T15:40:43Z by SystemAdmin
SystemAdmin
SystemAdmin
15496 Posts

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

‏2013-01-10T21:51:57Z |
Hi,

I am using Report Studio 8.4 to extract data.

I want to obtain the 2012 information of the following fields:

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. For example,
Store Number Store Size Code Sales 2012
116 A 57155
109 A 57202
114 A 57960
111 A 67199
118 A 67199
107 A 67914
106 A 67917
104 A 67932
117 A 67955
113 A 77199
115 A 77199
101 A 77920
112 A 87922
102 A 97955
103 A 233211
108 A 236709
110 A 332312
100 A 670689
105 A 745423
132 B 1670
126 B 37199
131 B 37199
120 B 37201
129 B 37947
125 B 37958
123 B 37964
130 B 47932
124 B 57201
127 B 57936
128 B 67932
122 B 137199
121 B 237936
119 B 337967
138 C 17958
141 C 27960
134 C 29679
133 C 37196
148 C 37196
139 C 37199
143 C 37199
145 C 37201
150 C 37201
144 C 37205
136 C 37922
142 C 37958
140 C 37960
147 C 37978
149 C 47924
135 C 57958
146 C 67201
137 C 77960

My questions are:
1) How to rank the Sales and divide them into quarters 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.
Updated on 2013-01-16T15:40:43Z at 2013-01-16T15:40:43Z by SystemAdmin
  • DanielWagemann
    DanielWagemann
    411 Posts

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

    ‏2013-01-12T12:28:29Z  
    I am not sure if you want to rank the entire thing first and then do the quarters or some other combinations. Also the following approach is in theory as I have not tested it.

    To get the quarters you could try getting the maximum(sales for Store Size): This should give you the maximum value for A,B and C.

    Once you have that you can take that calc and divide it by 4 to get your quarter values.

    Then create your buckets using an if then else.
    If sales is between the maximum value and the (maximum value/4)*3 then 4
    If sales is between the (maximum value/4)*3 and the (maximum value/4)*2 then 3
    and so on...

    To get your rank you can use the rank(sales for report) or rank(sales for Quarter) depending on what you need.
  • SystemAdmin
    SystemAdmin
    15496 Posts

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

    ‏2013-01-16T15:40:43Z  
    I am not sure if you want to rank the entire thing first and then do the quarters or some other combinations. Also the following approach is in theory as I have not tested it.

    To get the quarters you could try getting the maximum(sales for Store Size): This should give you the maximum value for A,B and C.

    Once you have that you can take that calc and divide it by 4 to get your quarter values.

    Then create your buckets using an if then else.
    If sales is between the maximum value and the (maximum value/4)*3 then 4
    If sales is between the (maximum value/4)*3 and the (maximum value/4)*2 then 3
    and so on...

    To get your rank you can use the rank(sales for report) or rank(sales for Quarter) depending on what you need.
    Thank you DanielWagemann,

    Will try your method ...