Print Page | Close Window

Count if Function in crystal?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12099
Printed Date: 26 Apr 2024 at 7:33pm


Topic: Count if Function in crystal?
Posted By: althomas
Subject: Count if Function in crystal?
Date Posted: 18 Jan 2011 at 5:05am
Hi,
 i was trying to create a report in which i want to count group totals only if they are greater than 0. Does anyone know how to write this formula.
 
I am trying to create a payment edit list in which the report pulls in all the invoices that are due for payment. Among these invoices, there are some credit memos which have negative balances and no checks will be be written. They are grouped by vendor totals (A vendor could have multiple invoices). I want to count the group total only if its greater than 0, in other words how many checks to write. since i am not going to write checks for credit memos (negative amounts), i dont want to include them in my could.
 
In excel i would've write is as : Countif (Vendor Group Total) > 0
 
Any help is greatly appreciated......


Replies:
Posted By: DBlank
Date Posted: 18 Jan 2011 at 5:29am
Running Total
field to summarize = vendor
type=DistinctCount
evaluate=use a formula...use your criteria here...like...SUM(sredit,vendor)>0
reset=never
place in report footer

Posted By: althomas
Date Posted: 20 Jan 2011 at 9:24am
I am a noob so, didnt quite understand what you wrote. i try to use 2 formulas
Checks to write:   If the Group total > 0 then 1 else 0
Summary : Sum (checks to write)
 
My problem is that it's not letting me sumarize the "checks to write" formula.
 
"Count" = if Sum ({APInvHed.InvoiceAmt}, {APInvHed.Calc_SupplierName})>0 then 1 else 0
 
The above formula just takes the goup total for each supplier and if that total is greater than 0 then 1      : Formula below was trying to sum this "count" formula of all the 1's.
 
"Sum": = mailto:sum%7b@count - sum{@count }
 
When i try to compile "Sum" i get the message : "The remaining text does not appear to be part of the formula"
 

Posted By: DBlank
Date Posted: 20 Jan 2011 at 9:47am
In the Field Explorer look ofr the Running Totals Fields section
Right click on it and select New
RT name = whatever
field to summarize = APInvHed.Calc_SupplierName
type of summary = DistinctCount
evaluate= select use a formula...click on the formula box... enter your criteria here...
Sum ({APInvHed.InvoiceAmt}, {APInvHed.Calc_SupplierName})>0
reset=never
place in report footer

Posted By: althomas
Date Posted: 20 Jan 2011 at 10:49am
Thank You so much!!!!!! i cant believe it worked..... Thanks again..

Posted By: althomas
Date Posted: 15 Feb 2011 at 5:52am
Hi,
 Would it be possible to help me with another question?
 
I wanted to conditional sum a group total.  In excel i would use the sumif function. I cant seem to find the sumif function in crystal.
 
What i want is the following:
 
Sum the Group Total if the Group Footer name is "x"
 
 
Thanks in advance for your help. Again i am a noobi so, please give me details.  thanks alot.

Posted By: DBlank
Date Posted: 15 Feb 2011 at 5:56am

can't really do that per se.

how are you determining the group footer "x"

Posted By: althomas
Date Posted: 15 Feb 2011 at 6:03am
I did a summary of the details and dropped it on the Group Footer ("X")  Then i dragged the column heading from my field explorer next to the sumarized footer. So now i have the Heading (dragged in from field explorer) next to Summary (of details of the heading)

Posted By: DBlank
Date Posted: 15 Feb 2011 at 6:11am
so now to go with the count you also need a corresponding SUM ?
make a new Running Total
RT name = whatever
field to summarize = APInvHed.InvoiceAmt
type of summary = SUM
evaluate= select use a formula...click on the formula box... enter your criteria here...
Sum ({APInvHed.InvoiceAmt}, {APInvHed.Calc_SupplierName})>0
reset=never
place in report footer
 
Dis that give you the expected amount?

Posted By: althomas
Date Posted: 21 Feb 2011 at 9:04am
Thanks for all your help. is there a special way to sum totals from main report and subreport to give a grant total? I feel bad asking you for every time, but if it's something easy then please help me...i want to learn Crystal report

Posted By: althomas
Date Posted: 21 Feb 2011 at 9:54am
i think i was able to figure it out!!!!!!
 
in the Main Report: 
 Created a formula as :
    Whileprintingrecords;
     Shared NumberVar TotalPrice := sum(MainReportField.TotalPrice)
 
in the subReport:
 Created a formula as :
  Whileprintingrecords;
 Shared NumberVar TotalPrice1 := sum(Subreportfield.TotalPrice)
 
In the Main Report:
created another formula:
WhilePrintingRecords;
Shared NumberVar TotalPrice;
Share Number var Totalprice1;
NumberVar GrandTotal;
GrandTotal := TotalPrice + TotalPrice1;
GrandTotal
 
Dropped it in the footer and both the main and sub report added up.


Print Page | Close Window