Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Getting a count of repeating rows into a report column

Received Response
1
Views
2
Comments
CArnold40
CArnold40 Rank 2 - Community Beginner
edited Aug 13, 2024 7:05PM in Oracle Analytics Cloud and Server

I have a request for a report from a client that wishes to show some Sales Agents activity -- their sales to all addresses, and then their sales to repeat addresses.

This is to gauge Sales Agents that have mutiple customers at the same addresses. 


If the data looked like this:

SalesAgent1

       999 Lion St

       999 Lion St

       999 Lion St

       555 Tiger St

       555 Tiger St

       777 Bear St

       222 Fox St

       444 Cat St

SalesAgent2

       888 Roger St

       888 Roger St

       333 Black St

       111 White St

       666 Blue St

They want the report to show something like below:

Sales AgentCount of UNIQUE AddressesCount of Repeat Addresses
SalesAgent152
SalesAgent241

The 'Count of UNIQUE Addresses' column will just give a count of all of the addresses (not a count of customers)

I know how to get the Count of UNIQUE Addresses:

  COUNT(DISTINCT "Customer"."Address")


The 'Count of Repeat Addresses' column needs to show a count of the addresses that have multiple customers -- so in other words, just a count of addresses that repeated.


The 'Count of Repeat Addresses' column is the one giving me problems. I feel like this should be a simple thing but I am making it too complex in my mind. I just cannot figure out how to do this part.

Is this even possible? If so, does anyone have any ideas as to how to achieve this?

Any help is greatly appreciated.

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Something similar to SUM(CASE WHEN COUNT(address BY address,sales agent) > 1 THEN 1 ELSE 0 END BY sales agent) should do the trick.

    Sent from my iPhone

  • CArnold40
    CArnold40 Rank 2 - Community Beginner

    That did it! THANK YOU!

    Here is how it ended up looking (in case you are interested)

    SUM(CASE WHEN COUNT(

    CONCAT(UPPER("Location"."Address1"), UPPER("Location"."Address2")) BY

    CONCAT(UPPER("Location"."Address1"), UPPER("Location"."Address2")), "Agent"."Agent ID"

    ) > 1

    THEN 1 ELSE 0 END BY

    "Agent"."Agent ID"

    )