Oracle Fusion Data Intelligence Idea Lab

Home› Oracle Analytics› Oracle Analytics Idea Labs› Oracle Fusion Data Intelligence Idea Lab

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

COUNTIF and COUNTIFS function for 'My Calculations'

Declined
37
Views
4
Comments
Andrew at MKL
Andrew at MKL Rank 6 - Analytics Lead
in Oracle Fusion Data Intelligence Idea Lab

Users in my organization have a need for a custom calculation that seems to only be possible using a COUNTIFS function. I'm basing this on my familiarity with the Excel function, and am requesting as such. If there is an alternative by which the same outcome can be accomplished I would be happy with that as well.

1
1 votes

Declined · Last Updated

Filter function is already available.

Comments

  • Megan Terrell
    Megan Terrell Rank 5 - Community Champion

    Try the Filter function.

    Filter

    This function generates a new filtered measure from the given measure and the given filter expression.

    FILTER(measure USING filter_expr)

    measure is the name of a measure column. filter_expr is a filter expression.

    Example: FILTER(revenue USING brand = 'abc')

  • Andrew at MKL
    Andrew at MKL Rank 6 - Analytics Lead

    @Megan Terrell I am specifically attempting to return a count of invoices based on their pay terms and how many have been paid on time vs not based on another custom calculation that I am working on. There isn't currently a measure by which to return a count to my knowledge.

  • Megan Terrell
    Megan Terrell Rank 5 - Community Champion

    Try using a Count Distinct for the invoice number. See below.

    (Filter(count(distinct(invoice #)) using [pay terms] in (X, Y, Z) AND [paid] = 'on time'))

  • Andrew at MKL
    Andrew at MKL Rank 6 - Analytics Lead

    @Megan Terrell thanks for this idea! The calculation validates appropriately, but I am getting an incorrect number count when comparing back to a detailed view that shows each invoice uniquely. I do get the same total number when using a regular Count(Distinct) function, so maybe I need to check the metadata. Regardless, I wanted to acknowledge that your suggestion was helpful - thank you!