Categories
- All Categories
- Oracle Analytics Learning Hub
- 17 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 224 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.8K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 84 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Is it possible to filter the results of an analysis using a calculated measure?
Hi,
I have a requirement to filter the report so that it only shows the items which have had <=2 transactions. When i created a column which has a count of transactions for each item and use the filter function, it is not working, and the result is still showing all items.
What could be the issue?
Thank you
Best Answer
-
You shouldn't care about grouping rows, because that's what the product does by default as soon as you do select a measure.
I finally got my hands on an environment to do a quick test: selection steps works but are generally used for operations on the dimensions.
In your case the filter on the measure should work...
The filter on the "2- Billed Quantity" is understood as being a filter on an aggregated measure, and the tool should translate correctly into a post-aggregation query.
The first part of the query does the aggregation, then the filter is applied on the result of the aggregation.
This is at least how OAS 2023 handles it, not sure about what version your OBI is.
Look at the generated query and if it doesn't behave correctly you maybe have issues in your model?
0
Answers
-
Aren't you facing a pre-aggregation vs post-aggregation filtering?
If you filter before grouping rows, your measure counting the transaction is probably 1 because each transaction is an individual row. While after grouping your rows based on your query, your measure counting now has a value based on the number of transactions grouped together.
It's a bit like the difference between a WHERE and a HAVING clause in SQL.
The easiest way to add a filter after the aggregation could be using the selection steps in your analysis.
0 -
Hi,
Thank you, @Gianni Ceresa ! I am now attempting to use selection steps to complete this task. However, it does not seem to apply the condition when I press ok.
Is there something I am configuring wrong here?
Also, what would be the method to group rows the way that you have mentioned?
Thank you for your help
0