10 Replies Latest reply on Jan 10, 2019 12:51 PM by ForSly

How to use filter function with group by

ForSly Jan 8, 2019 7:50 PM

I am trying to create a metric using filter function with a group by. I am not sure whether the filter function even works with group by in OBIEE answers. I need something like this ( Filter (#ex.XYZ Fatct group by a,b,c). my expected results is a count of all cases of yyFatct grouped by a,b,c

1. Re: How to use filter function with group by

Gianni Ceresa Jan 8, 2019 8:00 PM (in response to ForSly)

Hi,
Sorry but not sure to understand what you are after as your example isn't really OBIEE: you talk about the FILTER(.... USING ....) function?
Or you mean "filter" as a WHERE clause in a query?
Like Show 1 Likes(1)

Actions
2. Re: How to use filter function with group by

Robert Angel Jan 9, 2019 8:23 AM (in response to ForSly)

Is this what you are looking for ->

https://gerardnico.com/dat/obiee/obis/logical_sql/group_by
Like Show 0 Likes(0)

Actions
3. Re: How to use filter function with group by

ForSly Jan 9, 2019 11:12 AM (in response to Gianni Ceresa)

yes I am talking about FILTER(.... USING ....) function? can we use group by with this function? if so do you have an example?
Like Show 0 Likes(0)

Actions
4. Re: How to use filter function with group by

ForSly Jan 9, 2019 11:14 AM (in response to Robert Angel)

No, I am looking for something like this FILTER(.... USING ....)
Like Show 0 Likes(0)

Actions
5. Re: How to use filter function with group by

Christian Berg Jan 9, 2019 11:24 AM (in response to ForSly)

You're not providing much usable context, so here's gerard's wiki on FILTER:

https://gerardnico.com/dat/obiee/obips/filter_function

And the official documentation:

https://docs.oracle.com/cd/E23943_01/bi.1111/e10544/appuir.htm#BIEUG847
Like Show 0 Likes(0)

Actions
6. Re: How to use filter function with group by

Gianni Ceresa Jan 9, 2019 11:27 AM (in response to ForSly)

So you want to filter a measure by some values and in the same time group the result at a different level than the one of the analysis itself? Did you try it?
Can't the same be achieved with normal analysis filtering? (sometime FILTER USING is abused for no real reason)
Like Show 0 Likes(0)

Actions
7. Re: How to use filter function with group by

ForSly Jan 9, 2019 11:31 AM (in response to Gianni Ceresa)

I finally figured it out. using this (sum(count (distinct "Fact - XXY"."# Applicants" by "XXY"."Job ")) )
Like Show 0 Likes(0)

Actions
8. Re: How to use filter function with group by

Christian Berg Jan 9, 2019 12:16 PM (in response to ForSly)

What does that have to do with filtering?!
Like Show 0 Likes(0)

Actions
9. Re: How to use filter function with group by

Robert Angel Jan 9, 2019 1:21 PM (in response to ForSly)

Sum if versus filter by aside; if you have access to the rpd then you can duplicate your existing count of applicants measure, call it Count of Applicants by Job, pin (copy and paste) it to the job level (detail probably) of your associated dimension Job, then the number will be invariant for each job and you will have a measure that all of your users can use with simple logic, rather than a complex once over piece of logic that only you understand...
1 person found this helpful
Like Show 0 Likes(0)

Actions
10. Re: How to use filter function with group by

ForSly Jan 10, 2019 12:51 PM (in response to Christian Berg)

My thought process about using filtering was not going any where so I tried a different logic. sorry if I caused any confusion
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.