Subtracting presentation variable values — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Subtracting presentation variable values

Received Response
11
Views
3
Comments
mmajdana1
mmajdana1 Rank 6 - Analytics Lead

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    Filters do not execute arithmetic operations the way you're expecting:

    pastedImage_1.png

    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.

  • mmajdana1
    mmajdana1 Rank 6 - Analytics Lead

    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})

  • mmajdana1
    mmajdana1 Rank 6 - Analytics Lead

    P_GODINA = P_YEAR

    P_PRIKAZ_GODINA = P_REVERSE

    @Christian Berg