Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Is there a LOOKUP like function in OAC for dynamic calculations inside a table visual?

Received Response
85
Views
8
Comments
Rishabh Sood-Oracle
Rishabh Sood-Oracle Rank 2 - Community Beginner
in Oracle Analytics Cloud and Server

Hi team,

Is there a function in OAC/OAS that can be used to dynamically calculate values inside a table visualization? I’m trying to calculate the % difference and the difference from the previous year for the data in my visualization (please refer to the attached screenshots for more context). Is there something similar to Tableaus LOOKUP function in OAC that can help me with this?

My visual in OAC:

image.png

Example visual I am trying to replicate:

image.png

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited Nov 24, 2024 2:32PM

    Hi,

    There isn't a LOOKUP function (well, technically you could achieve something similar with FILTER(… USING …) if you really want something close to LOOKUP), but you don't need it.

    You have the time series function to easily get the measure one year ago, and then you can do any kind of calculation you need with it. Look for the AGO function, it will do the job.

  • Rishabh Sood-Oracle
    Rishabh Sood-Oracle Rank 2 - Community Beginner

    Thanks for the quick response, Gianni!! I'll give it a go and let you know if it worked.

  • Ram-Oracle
    Ram-Oracle Rank 6 - Analytics Lead

    Hi @User_WRABO , you may also please try other options like using filter function for difference and % difference.

    example:

    image.png

    filter(cast("Financials - AP Expense"."Facts - Analytics Currency"."Expense Distribution Amount" AS DOUBLE) USING (CAST("Financials - AP Expense"."Time"."Fiscal Period" AS INTEGER)=CAST("Financials - AP Expense"."Time"."Fiscal Period" as INTEGER) -1))

    image.png

    In my case I have to use cast function as it is giving errors like comparision is being carried between non compatible types.

    filter function syntax and usage:

    Filter

    This function generates a new filtered measure from the given measure and the given filter expression.

    FILTER(measure USING filter_expr)

    measure is the name of a measure column. filter_expr is a filter expression.

    Example: FILTER(revenue USING brand = 'abc')

  • [Deleted User]
    [Deleted User] Rank 7 - Analytics Coach

    @User_WRABO can you please update your profile in order to clearly identify yourself as an Oracle employee? Thank you.

  • Rishabh Sood-Oracle
    Rishabh Sood-Oracle Rank 2 - Community Beginner

    It looks like I’ve all the required fields filled on my profile but let me know if I missed anything! @Christian Berg -Oracle

  • [Deleted User]
    [Deleted User] Rank 7 - Analytics Coach

    Normally your display name is YourName-Oracle. Let's see if the admins can figure this out for you.

  • Rishabh Sood-Oracle
    Rishabh Sood-Oracle Rank 2 - Community Beginner

    I didn't know about that, but I found the option and updated my display name. Thanks!

  • [Deleted User]
    [Deleted User] Rank 7 - Analytics Coach

    Brilliant! 👍️