Oracle Communities

Oracle Analytics and AI

Child Item

COUNTIF and COUNTIFS function for 'My Calculations'

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.

Find more posts tagged with

Declined

Status: Declined

Comments

Megan Terrell

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

@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

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

@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!