Filtering data by aggregation like group by ...having count(*) >1 — Oracle Analytics

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

Filtering data by aggregation like group by ...having count(*) >1

Received Response
141
Views
7
Comments
fabryddorf-Oracle
fabryddorf-Oracle Rank 4 - Community Specialist
edited Aug 13, 2024 5:50PM in Oracle Analytics Cloud and Server

Hi All,

On the below report  I am trying to filter by guests who stayed in more than one hotel. Theoritically I should display only guest where the sum by guest >1

Any idea? I obtained the some simply in Pivot table on Guest name

aggregation.jpg

Answers

  • ArijitC
    ArijitC Rank 6 - Analytics Lead

    You can use selection step by clicking pastedImage_1.png in the analysis.

    Then do something like this ..

    pastedImage_2.png

    Thanks

    Arijit

  • fabryddorf-Oracle
    fabryddorf-Oracle Rank 4 - Community Specialist

    Good idea. I never explored this before ! Thanks.

    The measure option however doesn't offer to group by the SUM of stays and this is what I am looking for. They are only listed simple measures.

    Regards

  • ArijitC
    ArijitC Rank 6 - Analytics Lead

    Did you try selection on Guest Name column ?

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Just use the SUM(... by Guest) > 1 in your filter of the analysis.

    Just make sure the calculation is right by adding the formula in a column first and validate it, then you just filter on that column and then you can delete the column itself as the filter will copy the formula.

    Capture.PNG

  • Martin van Donselaar
    Martin van Donselaar Rank 6 - Analytics Lead

    You can try using something like COUNT (DISTINCT "Resort"  BY "Guest Name") > 1 OR SUM("Stays" by "Guest Name") > 1 in your filter section

  • fabryddorf-Oracle
    fabryddorf-Oracle Rank 4 - Community Specialist

    that was it

    COUNT(DISTINCT "Property"."Resort" by "Guests"."Guest Name" )

    Filtered >1

    Thanks again

  • fabryddorf-Oracle
    fabryddorf-Oracle Rank 4 - Community Specialist

    Thanks , that helped!

    The solution was

    COUNT(DISTINCT "Property"."Resort" by "Guests"."Guest Name" )

    and filter >1