cancel
Showing results for 
Search instead for 
Did you mean: 

Count Formula for a Distinct Count Column?

Former Member
0 Kudos
150

HI!  I am trying to arrive at a count, per each group header break down within my report, of all entries within a distinct count column that are greater than 1.  A distinct count column of entries is not included in the listing of items that are available via the Summary icon.  I then try via the formula function, but am having trouble creating the formula for this particular function, though.  Any suggestions?


Thank you!

Eric K.

Accepted Solutions (0)

Answers (2)

Answers (2)

abhilash_kumar
Active Contributor
0 Kudos

Hi Eric,

Something like this should work:

WhilePrintingRecords;

numbervar final_count;

If DistinctCount({Measure_field}, {Group_Field}) > 1 then

     final_count := final_count + 1;

Place this formula on the Group Header or Footer and suppress it.

Then, create a formula to display the total count and place this on the Report Footer:

WhilePrintingRecords;

numbervar final_count;


-Abhilash

abhilash_kumar
Active Contributor
0 Kudos

Lots of obvious errors in that code, Eric.

Please use this:

WhilePrintingRecords;

numbervar final_count;

If DistinctCount({tblPurOrdersHis.db_DateRec}, {tblPurOrdersHis.db_PONo})> 1 then

     final_count := final_count + 1;

-Abhilash

Former Member
0 Kudos

Abhilash,

Almost there!  Now getting the following pop-up when running the 'check formula wizard' prior to refreshing report data:

"The ) is missing"

and the program has the entire word THEN highlighted at the end of line 3 of 4 in our formula.

Can you let me know how to proceed?  Thank you.

Eric

abhilash_kumar
Active Contributor
0 Kudos

Hmmm..the code looks fine to me.

Are you using the same code I posted above or has it been modified too?

-Abhilash

Former Member
0 Kudos

WhilePrintingRecords;

numbervar final_count;

If DistinctCount (DistinctCount ({tblPurOrdersHis.db_DateRec), (tblPurOrdersHIs.db_PONo})> 1 then

  final_count := final_count + 1;

**I modified the group & filed selection to the include the actual field for my distinct count.  It was not 'liking' my formula when I was using the table groups alone for the DateRec and PO No selections.  That'd be the lone modification from your suggested formula that I made intentionally.

Let me know what you think. Thanks in advance!

Eric

abhilash_kumar
Active Contributor
0 Kudos

Why does your code have two DistinctCount functions?

Also databse fields are always enclosed within curly braces like this:

{tblPurOrdersHis.db_DateRec}


In your code above both the fields have a round brace - The first field has a round brace to the end and the next one has one at the start. Please correct that too.


Have a look at the code I posted above.


-Abhilash

Former Member
0 Kudos

Abhilash,

I think we're almost there.  I removed the errant duplication of the DistinctCounts word(s).  I also corrected the brackets surrounding the field we are looking to sum, per your suggestion.  It took to each of the changes favorably, but now gives me the following pop-up:

'The result of a selection must be boolean'.

Any suggestions on how to proceed given that message?

Thank you, in advance.


Eric

Former Member
0 Kudos

Abhilash,

If I have 3 purchase orders listed on my report (for example), each order containing 5 line items, I can mark (in Boolean fashion) an order with its line items showing as having been received on differing dates by the respective order's customer (a backordered PO) from those with all line items delivering on the same date. 

If I take that approach first, I can summarize the number of PO's within the list of 3 that are marked as containing 'Backorders'.  What formula do you suggest for arriving at Boolean result noting a PO with backordered line items, given the above description of the raw data in my report?

What formula would you use to sum the number of Boolean 'backorder' results, once the previous formula is in place?

This may be a better approach if we are at a dead end with our previous approach.  Let me know what you think.  Thank you in advance, Abhilash.

Have a great weekend.

Eric

abhilash_kumar
Active Contributor
0 Kudos

Getting back to the error on this formula. You shouldn't see that error if you've used the code in a new formula created via the 'Field Explorer'.

Have you used this code in the Record Selection or Group Selection Formula?

Please go to the Field Explorer > Right-click Formula Field > New and use the code we discussed above in this formula and place this on the Group Header of Group Footer.

Don't forget that there's one more formula you'll need to create and I've already posted the code for that too.

-Abhilash

Former Member
0 Kudos

Abhilash,

Works so great!  Thank you for helping me through it to completion.  I do have one last question: amongst the number of field selections that we counted with your formula, they are segmented by the 10 or 20 vendors from which we've ordered.  I am hoping to also include a sum of the results from our new formula for each vendor.  Is that something you can help with before you go?  Thank you, again, for everything thus far!

Eric

abhilash_kumar
Active Contributor
0 Kudos

Please post that as a new discussion (Per SCN rules) and provide more info on how the report is laid out. A simple mock-up of the report should be great too.

P.S: Don't forget to close this thread.

-Abhilash

Former Member
0 Kudos

Abhilash,

Thank you.  Still a bit new to this site; my apologies!  I opened a new discussion for next inquiry, per your suggestion.  You can view the new inquiry by following the link below:

http://scn.sap.com/message/15045416#15045416

Thank you, again.

Eric

Former Member
0 Kudos

Hi if you are trying to create a count in group level

Try this.

Create a formula something like below and place it in group header

WhilePrintingRecords;

if DistinctCount({Orders.Order ID}) > 1 then count({Orders.Customer ID},{Orders.Order ID})

Replace fields in above formula with your report fields

Former Member
0 Kudos

Vissu,

I like yours, Vissu; seems much simpler. The only thing is I'm getting a the following 'error message' when I go to check the validity of the formula before refreshing my report screen:  "There must be a group that matches this field" and it is leaving everything from 'then count...' over highlighted.  Any ideas for a workaround? 

Thank you, kindly!

Eric

abhilash_kumar
Active Contributor
0 Kudos

Although Vissu's code seems simpler it wouldn't show you what you're looking for.

If you wish to further summarize a summary field, you have to use variables and multiple formulas as suggested in my previous reply.

-Abhilash

Former Member
0 Kudos

Abhilash,

Thanks!  I am trying your formula but am getting the following when running the 'check formula for errors' feature prior to refreshing my data with the new field:

The pop-up box reads:

"The remaining text does not appear to be part of the formula"

and has the following portion of the formula you suggested highlighted:

= final_count +1;

Do you have any suggestion(s) on how to fix?

Thank you in advance.

Eric

abhilash_kumar
Active Contributor
0 Kudos

Strange! The code works fine on my machine.

Could you please paste the code you've used?

-Abhilash

Former Member
0 Kudos

Abhilash,

No problem!  Below:

WhilePrintingRecords;

numbervar final count;

If DistinctCount({tblPurOrdersHis.db_DateRec), (tblPurOrdersHis.db_PONo})> 1 then

     final_count ;= final count + 1;

Let me know what you think!  Thank you, sir!

Eric