Is there a way to do a conditional count? Count all of the child records only if they meet certain criteria?
Matt Cox shared this idea · Dec 12, 2016
Count records created in the last 90 days
Is there a way to do a conditional count? Count all of the child records only if they meet certain criteria?
Matt Cox shared this idea · Dec 12, 2016
Comments
1 comment
Hi Matt,
While we do not have countif() or sumif() functions to count or sum certain children, this is still possible by putting an if() statement in the children to "flag" them. In this case, we could use count() or sum() based on their functionality (count will count records that have a value in the desired field where null records are ignored).
1) If statement in the child table that references the record's created date being within the last 90 days. If the records is outside the 90 days, the formula outputs null() or 0. Null for a parent count or 0 if using the sum.
Calc Text: if( {Created} > datesub(today(), 90, "d"), "Yes",null())
Calc Num: if( {Created} > datesub(today(), 90, "d"), 1,0)
2) In the parent table, use a count or sum on the child field from step 1. Count on the calculated text or sum on the calculated number.
Hope this helps!
Alex
Please sign in to leave a comment.