Difference between revisions of "How do I do a count using a sum?"
(→Examples) |
|||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| + | __NOTOC__ | ||
'''Q: How do I do a count using a sum? | '''Q: How do I do a count using a sum? | ||
| − | ''A: <nowiki>if([this]='Counting Criteria',1,0)</nowiki> | + | ''A: <nowiki>if([this]=</nowiki>'' 'Counting Criteria' ''<nowiki>,1,0)</nowiki>'' |
'''Why do we do this? | '''Why do we do this? | ||
| Line 12: | Line 13: | ||
[[Image:ReportBuilderExample1.PNG|link=|300px]]<br> | [[Image:ReportBuilderExample1.PNG|link=|300px]]<br> | ||
This calculation will assign a value of "1" to each record where the @userid@ matches the Owner ID (in other words, all records where the user currently logged in is the Owner of the record), and a value of "0" to all other records, and then sum up the list, returning a total of records for which the current user is the Owner. | This calculation will assign a value of "1" to each record where the @userid@ matches the Owner ID (in other words, all records where the user currently logged in is the Owner of the record), and a value of "0" to all other records, and then sum up the list, returning a total of records for which the current user is the Owner. | ||
| + | |||
| + | ===Counting Records where End Date is After a Given Date=== | ||
| + | [[Image:ReportBuilderExample2.PNG|link=|300px]]<br> | ||
| + | This calculation will assign a value of "1" to each record where the End Date is later than April 1, 2013, and a value of "0" to all other records, and then sum up the list, returning a total of all records for which the End Date is later than April 1, 2013. | ||
| + | |||
| + | ===Counting Records where Text Field contains a Given Text String=== | ||
| + | [[Image:ReportBuilderExample3.PNG|link=|300px]]<br> | ||
| + | This calculation will assign a value of "1" to each record where the Description field contains the word "laptop," and a value of "0" to all other records, and then sum up the list, returning a total of all records where the Description field contains the word "laptop." | ||
| + | |||
| + | ==See Also== | ||
| + | * [[Calculated Field]] | ||
[[Category:FAQ]][[Category:Reports]] | [[Category:FAQ]][[Category:Reports]] | ||
Latest revision as of 10:40, 26 April 2013
Q: How do I do a count using a sum?
A: if([this]= 'Counting Criteria' ,1,0)
Why do we do this?
When using the Criteria line in the Report Builder, one line of criteria is treated as a single AND statement. By using this method we can move the criteria up to the Calculate Field area and allow for multiple OR criteria using a singular AND statement in the Criteria line.
Examples
Counting Records where Current User is the Owner
This calculation will assign a value of "1" to each record where the @userid@ matches the Owner ID (in other words, all records where the user currently logged in is the Owner of the record), and a value of "0" to all other records, and then sum up the list, returning a total of records for which the current user is the Owner.
Counting Records where End Date is After a Given Date
This calculation will assign a value of "1" to each record where the End Date is later than April 1, 2013, and a value of "0" to all other records, and then sum up the list, returning a total of all records for which the End Date is later than April 1, 2013.
Counting Records where Text Field contains a Given Text String
This calculation will assign a value of "1" to each record where the Description field contains the word "laptop," and a value of "0" to all other records, and then sum up the list, returning a total of all records where the Description field contains the word "laptop."
