Effective Formula for Percentage Field from 2 DistinctCount Fields?

[Former Member]

Hi guys,

Any ideas for an effective formula for calculating the percentage of 1 distinct count field from another distinct count field?

The stab I took at it was a miss.

Thanks!

Eric

[abhilash_kumar · Accepted solution]

Hi Eric,

Do you wish to display the percentages on each row or just on the last row?

Please paste the code you already have for both the distinct counts.

-Abhilash

[Former Member · Accepted solution]

Abhilash,

The first field within the 2 from which I hope to derive the percentage field is an 'insert summary' field in Group Footer No.1.  It is a DistinctCount summary of total number of PO's in my report ordered from a particular vendor.  The 2nd field allows us to see how many of those PO's, per vendor, had backordered items on them. That field is derived from the following formula:

1.  Group Header No. 1:

     WhilePrintingRecords;

          numbervar cnt:=0

2.  Group Header No. 2

     WhilePrintingRecords;

          numbervar cnt=

               numbervar cnt2=

          if (BO?) =1 then (cnt := cnt+1; cnt2; = cnt2 +1);

3.  In Group Footer No.1:

     WhilePrintingRecords;

          numbervar cnt;

I'd like to have a percentage field within each group/vendor footer, that finds the percentage amount of 100 - 1st field/2nd field.

Ultimately, would like to add a report total for both fields in the report footer, as well.  The first field formula is the same in the current report footer, while the 2nd field formula is as follows:

1. Report Footer:

     WhilePrintingRecords;

          numberbar cnt2;

Thank you, Abhilash!  Have a good day.

Eric

[abhilash_kumar · Accepted solution]

OK. Here's what you need to do:

1) Create this formula and place this on the Group Footer #1:

EvaluateAfter({@Formula_Name_in_Step2});

numbervar cnt;

(DistinctCount({PO}, {Vendor_Group_Field}) / cnt) * 100;

Note: Replace {@Formula_Name_in_Step2} with the name of the formula you've created in Point 2 above.

2) Create another formula and place this on the Report Footer:

WhilePrintingRecords;

numberbar cnt2;

(DistinctCount({PO}) / cnt2) * 100;

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Almost!  I am getting a pop-up box error message when I click the formula check button:

"The ( is missing"

Upon display of this message, my report is highlighting the comma before {Vendor_Group_Field} in line 3 of our Group Footer No. 1 formula.  Suggested corrective action?

Thank you!

Eric

[Former Member · Accepted solution]

Friends,

Abhilash may be out on business or pleasure.  Any chance for suggested remedy to my above post [in his absence]?

Thank you!

Eric

[abhilash_kumar · Accepted solution]

Hey Eric,

Could you paste the code you have in your formula editor please?

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Certainly.  Currently, I have the following loaded in the Group Footer #1 for our percentage formula:

EvaluateAfter({Sastry - Detail Section});

numbervar cnt;

(DistinctCount ({tblPurOrdersHis.db_PoNo}), GroupName ({tblvVendorMstr.db_Name})  cnt) * 100;

As a refresher, I am getting the pop-up message mentioned in my most recent post with a highlighted cursor in line 4 at the comma before GroupName, etc.

Thank you, my friend!  Have a great day.

Eric

[abhilash_kumar · Accepted solution]

Change the code to:

EvaluateAfter({Sastry - Detail Section});

numbervar cnt;

(DistinctCount ({tblPurOrdersHis.db_PoNo}, {tblvVendorMstr.db_Name}) / cnt) * 100;

What is {Sastry - Detail Section}? I'm assuming that's a formula. In which case, you would need to change it to:

EvaluateAfter({@Sastry - Detail Section});

-Abhilash

[Former Member · Accepted solution]

Abhilash,

The formula is checking out now upon click of the 'check formula' button.  However, when I go to 'refresh data' in the preview screen, I am getting the following pop-up box message:

'Division by zero.'

I've never seen this message before.  Not sure how to proceed. It is then highlighting the following portion of your suggested formula:

(DistinctCount ({tblPurOrdersHis.db_PoNo),(tblvVendorMstr.db_Name})/cnt)*100;

Thank you, Abhilash!  Have a great day.  Thank you, as always, for your help.

Eric

[Former Member · Accepted solution]

Abhilash,

I hope you enjoyed your weekend!  Was hoping you may able to address my most recent inquiry above at some point today.  Would be greatly appreciative, if so.

Eric

[abhilash_kumar · Accepted solution]

Hi Eric,

Thanks, the weekend went just fine!

First of all, please note that database fields are always supposed to be enclosed in parenthesis of this type - '{' and '}'. I've observed, even in your previous posts, that you somehow seem to miss these parenthesis for the database fields.

You really don't need to type the fields name. You just have to double-click the fields name from the 'Report Fields' panel in the formula editor.

Back to the error then; it indicates that the variable 'cnt' is zero. Change the code to:

EvaluateAfter({Sastry - Detail Section});

numbervar cnt;

if cnt = 0 then 0 else

(DistinctCount ({tblPurOrdersHis.db_PoNo}, {tblvVendorMstr.db_Name}) / cnt) * 100;

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Your suggested adjustment worked perfectly!  Is it possible to, now, include a percentage for the measures in the Report Footer section?

Total Number of PO's for Entire Report:

     DistinctCount({tblPurOrdersHis.db_PoNo})

Total Number of Backorders for Entire Report:

     Whileprintingrecords;

     numbervar cnt2;

Thank you!  Let me know if I need to start a new thread for this one.  Will be happy to, if so.

Eric