| Author |
Message |
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
   Topic: Get Top 10 ranking based on formula field
Posted: 07 Jul 2010 at 7:51am |
Hi there,
I need help in sorting my data ranking based on a formula(Avg Sales). Below is what I want to achieve.
Shop Avg Sales Ranking
A 400 1
B 300 2
C 200 3
.
.
.
I try to use Group Expert but I cant see the formula listed in there. Can anyone of you give me some advice on how to achieve that results?
Thanks in advance..
Elisha  |
|
3Lish@
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Jul 2010 at 8:01am |
|
You have to use an insert summary for your average sales grouped on shop field. Then you can use the Group Sort as as a Top N (10)
Edited by DBlank - 07 Jul 2010 at 8:01am
|
|
IP Logged |
|
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
Posted: 07 Jul 2010 at 4:16pm |
Hi DBlank..
I manage to get the sorting if it just for Average sales ranking. But now Im having problem is the sales ranking is based on a formula = Avg sales / Area.
If I use this formula, then Im not able to do the group sorting. Can u advice me on this?
Thanks in advance..
Elisha  |
|
3Lish@
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Jul 2010 at 3:19am |
Sorry but you will not be able to do that in Crystal (at least I do not know how). Top N sorting is only available if using the Insert Summary function.
YOu can manually sort your groups but that won't work as your sales figures change.
What is the "Area" field that youa re using to divide your average with?
|
|
IP Logged |
|
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
Posted: 08 Jul 2010 at 5:32am |
DBlank,
The "Area" field is each shop area. In order for me to get the average sales per square feet for each shop, i need to get the AVG sales/shop area.
So, what can I do now? Kindly advice..
Thanks in advance..
Elisha  |
|
3Lish@
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Jul 2010 at 5:51am |
logically I think you can make a formula field as
Table.sales/area (calcualting per data row)
then insert a SUmmary as the Aerage of this formula which will allow you to sort your group on that using Top N sorting
Edited by DBlank - 08 Jul 2010 at 5:54am
|
|
IP Logged |
|
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
Posted: 08 Jul 2010 at 7:40am |
DBlank,
Thanks for the solution. I manage to do the ranking already. Really appreciate your help.
Have a nice day~
Elisha  |
|
3Lish@
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Jul 2010 at 7:41am |
Can you post your solution so others can learn from it?
Thanks
|
|
IP Logged |
|
Elisha83
Groupie
Joined: 19 Feb 2008
Location: Malaysia
Online Status: Offline
Posts: 62
|
Posted: 08 Jul 2010 at 4:15pm |
DBlank,
Here is what i done. Firstly, I use each subsequent 12 months sales for each shop divided by area. Then use Summary Expert function and choose Average for that formula field. Put at Shop grouping level.
Then go to Group Sort Expert, sort by "Top N" and choose that Average formula. By doing this, I manage to achieve the ranking sorting. 
Thanks a lot for giving me the idea.. |
|
3Lish@
|
|
IP Logged |
|
|
|
