Get Top 10 ranking based on formula field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=10452
Printed Date: 18 May 2024 at 10:51pm
Topic: Get Top 10 ranking based on formula field
Posted By: Elisha83
Subject: Get Top 10 ranking based on formula field
Date 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@ |
Replies:
Posted By: DBlank
Date 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) |
Posted By: Elisha83
Date 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@ |
Posted By: DBlank
Date 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?
|
Posted By: Elisha83
Date 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@ |
Posted By: DBlank
Date 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
|
Posted By: Elisha83
Date 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@ |
Posted By: DBlank
Date Posted: 08 Jul 2010 at 7:41am
|
Can you post your solution so others can learn from it?
Thanks
|
Posted By: Elisha83
Date 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@ |