Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Creating a Calculation that Ignores Filters in OAC
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?
Answers
-
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
Oracle Analytics - Building a Filter-Selective Calculation Part II
Regards,
Ezequiel.
0 -
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:
Can something similar be done in Oracle DV?
Best Regards.
0 -
Hi @User_LWG2Y ,
Did you find a solution?
0 -
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
0 -
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
1 -
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
0 -
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!
0 -
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?
0 -
@Bret Grinslade - Oracle Analytics-Oracle , both cases would be useful! In this example, it is indeed a YTD metric.
0 -
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
0
