We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

Using the Excel Function - COUNTIF

Comments

3 comments

  • Avatar
    Adrian Williams

    The AggEx mode provides functionality equivalent to the Excel CountIF.

    The groupCount() operator provides the count of the items that match the configured criterion. The groupCount() operator can accept a filter predicate e.g. groupCount(Item_Number == 101). However, as with the Excel CountIF function, this would only generate the count for the number of items that matched the specified  predicate. Additional aggregate expressions would be required for each distinct predicatethat needed to be counted.

    Instead of using this approach it is simpler to just use the groupCount() operator without a specified filter predicate and then define the GroupBy property to identify the field whose values will be used as the predicates.

    Which results in the following output data:

    A Lookup node can be used to join the output of the AggEx node with the original data to generate the required results.

     

    The groupCount() operator can also be used in a Filter node to generate a running total for a specified predicate.

     

     

    Attached files

    Count_Items_in_Group.brg

     

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    As an aid to users migrating to the Data360 Analyze product, a similar approach would be used in Data360 Analyze but using the Aggregate node instead of the AggEx node.

     

     

    Attached files

    Count_Items_In_Group - 26 Aug 2021.lna

     

    0
    Comment actions Permalink
  • Avatar
    andrew darnell

    Hi Adrian,

    Thanks so much for that, works perfectly :)

    Also for putting an explanation 

     

    0
    Comment actions Permalink

Please sign in to leave a comment.