Group by Formula

[alvintse]

The report will returns the records for the sales of each agent they made for the specific peroid of time.
The report will list out summary of agent's data with the heading as below:
- Inactive agent
- New agent
- Quartile I (top 25% agent)
- Quartile II (
- Quartile III
- Quartile IV

I am thinking group by formula, there is no problem to create the groups Inactive agent and New agent. For the rest, how do I check how many agents there, sum the sales for each agent, and assign them to the correct Quartile within the group formula?

I hope you can understand my question and any suggestion will be helpful.

 

[Madawc]

It depends on how you find the % agent. If it's based on comparisons with other records, then you won't know it until you're past the stage at which Crystal calculates groups.

Madawc Williams
East Anglia, Great Britain

 

[alvintse]

Yes, that is my problem. Say if I know there is total 100 agents, then Quartile I will display the top 25 agents. Quartile II will display the next 25 top agents, and so on...
Any suggestion on how do I make it happens.

Thanks.

 

[Ngolem]

- Quartile I (top 25% agent)
- Quartile II (
- Quartile III
- Quartile IV

these are not individual groups but rather the results of one group, yes?

Same thing with Agents You have Inactive and New Agents (kinda wonder about "currently active" agents)

As I see it you have 2 groups

Group 1 agents
Group 2 quartile

Perhaps you can use TOP N summary of agents by quartile

Jim Broadbent

 

[alvintse]

I am not sure if I missed something, Top N Summary will only display Top N records, and you cannot enter the (N) drammically. Beside, all other records will discarded.

Now I create a formula with for group1 with Agent, Quartile.
I also add another group with AgentID

Now the report will be something like this:
- Agent (GH1)
(GF2)AgentID 1 ........... Total Amount
2 ........... Total Amount
_____________________________________________
Total Grand Total

- Quartile(GH1)
-(GF2) AgentID 4 ........... Total Amount
6 ........... Total Amount
8 ........... Total Amount
............
______________________________________________
Total Grant total

I used a stupid way by counting the number of agents and divided the total number of records showed at Quartile.
Say if there are 80 agents, then each quartile will contain 20 records. Then, I add couple section below GF2 and manually type in Quartile II,III and IV, conditionally suppress them. The layout is about right.
But another problem arise, I cannot use a running total to calculate the grand total for the Quartiles except Q1 which is the beginning of group.
The reason is I cannot reset the running total base on the change of group nor ask crystal reports to evaluate only the records start from specific point because all the records are actually belong to the same group.

Any idea on how to solve this?

Thanks a lot!!

 

[lbass]

You should use topN to sort your records by the sales amount field--just choose "All" instead of setting "N" = to a specific number. If you choose descending order, then you could do something like the following to define your quartiles. First create a running total {#countagent}. Using the running total editor, choose {table.agent}, count, evaluate on change of group (agent), reset never.
Then create a formula {@quartile}:

if {#countagent} in 0 * distinctcount({table.agent}) to .25 * distinctcount({table.agent}) then "75th Percentile" else
if {#countagent} in .25* distinctcount({table.agent}) to .50 * distinctcount({table.agent}) then "50th Percentile" else
if {#countagent} in .50* distinctcount({table.agent}) to .75 * distinctcount({table.agent}) then "25th Percentile" else
if {#countagent} in .75* distinctcount({table.agent}) to 1.00 * distinctcount({table.agent}) then "0 Percentile"

You might have to adjust this formula based on how you treat percentiles. Also, if you are nesting the topN and quartiles within higher order groups, you would need to reset the running total on change of group and add in a group condition into the distinctcount component of the formula.

If you were working with fields instead of summaries, you could use the PthPercentile function, but I don't think there is a way to use that with summaries.

-LB

 

[Ngolem]

Personally I think you should step back and rethink your approach.

*********************

- Agent (GH1)
(GF2)AgentID 1 ........... Total Amount
2 ........... Total Amount
_____________________________________________
Total Grand Total

- Quartile(GH1)
-(GF2) AgentID 4 ........... Total Amount
6 ........... Total Amount
8 ........... Total Amount
............
************

This makes no sense to me at all

***************************
Say if there are 80 agents, then each quartile will contain 20 records.
***************************

Cannot an agent sell in more than one Quartile??

rather than wave your hands in the air....give us a sample of your expected output and the tables from which you get the data.

I see no way that you can have both Agent and Quartile as Group one....there is no relationship...that I can see...and I was once a sales manager for 15 years



Jim Broadbent