Count of a distinct count?? 1

[Csukardi151]

Hello all,

I'm gonna try my best to explain my situation. I have a report that lists companies and the number of orders they have had (within a certain date). The number of orders are listed as a distinct count.

Let me show an example then explain what I am trying to accomplish.

---------
Example:

*Company* *Orders*
Company #1 10
Company #2 5
Company #3 3
company #4 1
Company #5 1

Total Companies: 5 Total Repeat orders: ??? <- this is where the problem lies.
---------

What i'm looking to do is get a distinct count of all orders that are above 1 (so a Distinct count of a distinct count.. in a sense). What I currently tried doing was creating a forumla that says:

if DistinctCount ({V_ORDER_HIST_LINE.DATE_SHIPPED}, {V_ORDER_HIST_LINE.NAME_CUST_SHIP}) > 1 then 1

Which will put a 1 next to any company that has more than 1 order (which would be Companies 1, 2 and 3 according to the example).

Now I want get a count of those companies that are 2+ orders. However I cannot do a distinct count of that formula I created.

Is there a different way of going about this? I hope I was detailed enough. Please let me know if there is anything that needs clarification!


Thank you,
Steve

 

[lbass]

Create a formula like this:

whileprintingrecords;
numbervar cntcos;
if DistinctCount ({V_ORDER_HIST_LINE.DATE_SHIPPED}, {V_ORDER_HIST_LINE.NAME_CUST_SHIP}) > 1 then
cntcos := cntcos + 1;

Place this in the company group section. Then in the report footer, use a formula like this:

whileprintingrecords;
numbervar cntcos;

-LB

 

[Csukardi151]

Thank you very much for the reply! That is really close to what i'm looking for! I guess I did leave out a tiny bit of detail which was my fault.

The count is working great as it adds up the amount per each one that is 2 or above, however, the companies are listed in group footer 2 and the distinct count i wanted listed in group footer 1 (as the companies will be a drill down).

The formula you supplied definately works, but when placed in group footer 1, it counts each drill down so the first drill down category will have the right amount listed, but the second category will have its own count plus the count added from the first category and so on and so on.

It amazes me how much detail can go into formula's and such on Crystal Reports.

 

[lbass]

Add a reset formula to the group header #1:

whileprintingrecords;
numbervar cntcos;
if not inrepeatedgroupheader then
cntcos := 0;

-LB

 

[Csukardi151]

Simply amazing, works flawlessly. Thanks again LB!