Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Subtracting presentation variable values
Hi i am using OBIEE 12.2.1.2.0
I have a situation where i have a set of years as a column in my pivot
On my dashboard page i have a year prompt and prompt for how many years in reverse i should show the data. Those two years are different columns one is for period and one is a business value.
So i have a year and presentation variable P_YEAR and prompt for how many years in reverse P_REVERSE
p_year is numeric in Physical layer and in database, p_reverse is a custom valu but also set as number
So i made sql filter in my analysis
''PRODUCT AGGREGATION''.''YEARS'' >= @{P_YEAR} - @{P_REVERSE}
And it throws error back and shows this in error log
''PRODUCT AGGREGATION''.''YEARS'' >= 2019-3
instead of
''PRODUCT AGGREGATION''.''YEARS'' >= 2016
How can i achieve this
Kr, Matija
Answers
-
Filters do not execute arithmetic operations the way you're expecting:
Also the "logic" implemented doesn't make a lot of sense.
Case Year = 2019 and offset = 5 ==> Your logic would show years >= 2014, so 2014, 2015, 2016, 2017, 2018, 2019
Case Year = 2016 and offset = 2==> Your logic would show years >= 2014, so 2014, 2015, 2016, 2017, 2018, 2019
Case Year = 1999 and offset = 1 ==> Your logic would show years >= 1998, so 1998, 1999, .......2019. 21 years.
You should maybe think about what you are trying to do again. Combining and offset with >= seems not very logical in this case. Just say "show all as of 1998" and you have the range without any weird arithmetic.
0 -
Hi, thank you for your effort.
As i said ''Those two years are different columns one is for period and one is a business value.''
it is impossible from a business point of view that business value is greater than period so the scenarios you described will not happen.
I managed to do it this way ( filtered column is dynamical based on prompt value)
case when '@{P_PREGLED}' = 'Po datumu osnivanja'
then "Predmet agregacija"."Predmet osnivanje godina" /*Business year1*/
when '@{P_PREGLED}' = 'Po datumu početka procesa' /*business year2*/
then "Predmet agregacija"."Predmet pocetak procesa godina"
end > (@{P_GODINA})- (@{P_PRIKAZ_GODINA}{100})
0 -
0