Oracle Communities

Oracle Analytics and AI

Child Item

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

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

Find more posts tagged with

Accepted answers

All comments

ArijitC

You can use selection step by clicking in the analysis.

Then do something like this ..

Thanks

Arijit

fabryddorf-Oracle

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

Did you try selection on Guest Name column ?

Gianni Ceresa

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.

Martin van Donselaar

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

that was it

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

Filtered >1

Thanks again

fabryddorf-Oracle

Thanks , that helped!

The solution was

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

and filter >1