Author |
Message |
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Topic: Grouping by a formula Posted: 19 Aug 2010 at 11:24am |
I have a database that includes customers and their information, along with the total price of different orders they’ve made. I’m trying to list how many customers had the total of all their orders in different ranges (i.e.- 50 customers had orders that totaled between $5000-$7500). I made a formula to calculate the total price of all the orders from each customer, but crystal won’t let me group by my formula. At this point, I’m only able to list how many of the individual orders fell into each range, but that doesn’t give me the number of customers in each range.
My formula looks like this: Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}), where the first field is the individual order total, and the second field is the customer ID. I’ve checked the data and the formula works correctly.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 19 Aug 2010 at 11:47am |
You cannot group by this formula because the returned values from the formula are dependent on the existing group structure.
You can do both of these:
sort using TOP N Group sorting
and/or
count the customers in each 'range' using Running Totals (or variable formulas, both of which need to be displayed in the report footer)
Edited by DBlank - 19 Aug 2010 at 11:49am
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 31 Aug 2010 at 7:12am |
I tried using running totals, but it would still only allow me to count the individual order totals, not the total of each customer's orders. I would need to use a running total based on my formula, which I can't do.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 7:17am |
RT example
Name=5000to7500 Field to Summarize=custmerID (or name if you do not have an ID) Type=distinctCount Evaluate=use a formula Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) in 5000 to 7500 Reset=Never Place in Report footer
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 31 Aug 2010 at 7:40am |
Thank you so much!
One question- should I use "count" instead of "distinct count" since I want to include duplicate totals?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 7:42am |
can you explain that further?
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 31 Aug 2010 at 7:57am |
I'm counting the number of customers who had orders totalling in between 2500 and 5000, for example. If there are two customers who had orders totalling $3000.50, then I would want to count both of those customers.
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 8:06am |
Use distinctCount of the customerID
That will count each customer once (per running total you create).
If you use count it is going to count them per data row so your number will explode.
If it does not give you the expected correct c**ts tehre are some other tricks to use but from what you have explained to me it should be fine.
Edited by DBlank - 31 Aug 2010 at 8:07am
|
IP Logged |
|
Megan10e
Newbie
Joined: 19 Aug 2010
Online Status: Offline
Posts: 24
|
Posted: 31 Aug 2010 at 8:19am |
Okay I see.
Also, my last range should be greater than 100,000. What do I use instead of "in...to" in the formula?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2010 at 8:24am |
Sum ({HORDHDR.ORDERTOTAL},{HORDHDR.CUSTNO}) > 100000
be careful with the In x to y as it include both ends of the spectrum
meaning if you have one as
in 5000 to 7500
and another as
in 7500 to 10000
and you have a customer with exactly 7500 as their sum they will be counted in both of the groups
you need to do
in 7501 to 10000
to avoid double counting
|
IP Logged |
|
|