How to get delta value from a measure column — Oracle Analytics

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

How to get delta value from a measure column

Received Response
31
Views
5
Comments
User_CEU9I
User_CEU9I Rank 5 - Community Champion
edited Aug 13, 2024 6:16PM in Oracle Analytics Cloud and Server

Hi all,

I would like to display delta value in the following scenario like this 8-2 =6.

The value 6 should be displayed in the next row. How to achieve this. Please advice.

Capture1234.PNG

Regards,

Chandra kanth.

Answers

  • AkanshAgarwal
    AkanshAgarwal Rank 4 - Community Specialist

    Hi Chandra Kanth,

    You can do it by creating a calculated column on an dimension.

    04072016.png

    Regards,

    Akansh

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead


    Hello Chandra,

    You could use analytics functions, with LAG and LEAD.

    Formulas:


    1- Purchase Order:  "ORDER"."PURCHASE ORDER"

    2- Value Order:  "EXECUCION"."VALUE ORDER"

    3- Lag Column:   EVALUATE('LAG(%1,1) over (order by %2)' as double,  "EXECUCION"."VALUE ORDER" , "ORDER"."PURCHASE ORDER")

    4- Delta Columna:  "EXECUCION"."VALUE ORDER"  -  (EVALUATE('LAG(%1,1) over (order by %2)' as double,  "EXECUCION"."VALUE ORDER" , "ORDER"."PURCHASE ORDER"))

    Results.jpg

    AS your question do something like this,your delta Formula:

    "Total Actions Count" - (EVALUATE(LAG(%1,1) OVER (order by %2)' as double, "Total Actions Count", "Plan"  ))

    Tip: To use EVALUTE FUNCTION you have to set up your NQSConfig.INI, set up the EVALUATE LEVEL.

    Kind Regards.

    César

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi cesar,

    The output should be displayed below (next row) NOT in next column. How to get value in the next row.

    Regards

    Chandra kanth.

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello chandra,

    This delta row its just for this two plans? 28 29?

    Or there is a possibility that you could compare 3, 4 plans?

    Kind regards,

    Cesar

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Your use of information drives your physical data model design ... you need a fact table that is keyed to a dimension that has each iteration value ... you will calculate each row and the delta to it's corresponding row ... now you can show either value or both.