nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Is it possible to filter the results of an analysis using a calculated measure?

Mariyam129

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

Find more posts tagged with

Oracle Fusion Analytics

OTBI

Accepted answers

Gianni Ceresa

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?

All comments

Gianni Ceresa

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.

Mariyam129

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

Gianni Ceresa

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?