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

Creating a Calculation that Ignores Filters in OAC

User_LWG2Y

How can I create a calculated measurement in DV omitting some filters?

I need to add a field called BUDGET and I want the calculation not to be affected by some filters.

In power bi they use this function CALCULATE(SUM('BASE'[BUDGET]),REMOVEFILTERS(BASE[Exclude_filters]),'CALENDAR()'[Month])

In Qlik sense they use this function SUM({<Exclude_filters>}BUDGET)

How can I do something similar in Oracle DV?

Find more posts tagged with

Accepted answers

All comments

EzequielC-Oracle

Hello,

That is explained in these 2 videos. Please, check if that would meet your business requirement:

Oracle Analytics - Building a Filter-Proof Calculation Part I

https://www.youtube.com/watch?v=0D9VKHy4968

Oracle Analytics - Building a Filter-Selective Calculation Part II

https://www.youtube.com/watch?v=Dequ7RwKrTE

Regards,

Ezequiel.

User_LWG2Y

Hello EzequielC,

Thank you very much for the reply.

But it's not really what I'm looking for.

To give more detail about what needs to be done, it is that a calculated measure is not affected within the same graph:

For example I have the following table:

where we have the address dimension and it makes the sum of the BUDGET and then it makes the sum of the BUDGET but omitting the month filter

Which results in the last calculated field not being affected by the month filter as shown in the images

This is the link to the function in qlik sense:

https://community.qlik.com/t5/New-to-Qlik-Sense/How-is-quot-Ignore-quot-a-single-in-set-filters-in-Calculated/td-p/1859929

Can something similar be done in Oracle DV?

Best Regards.

User_WQKHO

Hi @User_LWG2Y ,

Did you find a solution?

Mostafa Morsy-Oracle

@User_LWG2Y

I created a use case based on the Screen shots you attached

1- First created a Parameter to hold the Filter values

2- Create Dashboard Filter to select the Parameter values

3- Create your Calculated Item as follows Please Adjust the logic based on your Use case

Let me know if this works for you

User_WQKHO

Hi @Mostafa Morsy-Oracle

Not the original poster but I believe the need is to have a calculation that bypasses prompt filters. For example, a cumulative sum by year that would be accurate even though you are not selecting a full year in the dashboard prompts. The exclude filters you've suggested are already natively available

Mostafa Morsy-Oracle

@User_WQKHO

so we will have two calculated items one as described in the above post to get the SUM NOT including the Filter and the other One which has the SUM without the Filter

this workaround based on the screen shots uploaded

User_WQKHO

Mostafa Morsy-Oracle,

Thanks, this works but only if you're using the calc by itself.

See, I have the following table, where the cumulative count is a calculation done using rsum(count by year)

Now if a user would select the month of May, the desired effect is that you would only see the month of may in that table, the count would show 4M but the cumulative count would show 21M (ignoring the month selection). Currently, the calculation will show a cumulative sum for May only (which I understand why), but I would need that cumulative sum to bypass the month filter, if possible!

Bret Grinslade - Oracle Analytics-Oracle

What does the Cumulative count mean hear — year to date? For example if you pick may and have data from Dec 2021 — should that be in the amount?

User_WQKHO

@Bret Grinslade - Oracle Analytics-Oracle , both cases would be useful! In this example, it is indeed a YTD metric.

User_WQKHO

Hi @Bret Grinslade - Oracle Analytics-Oracle , just following up, do you have any idea for this? TODATE() would work, but I do not have a date hierarchy in this scenario. Thanks