Help with Formula Syntax please — 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

Help with Formula Syntax please

Received Response
11
Views
3
Comments
gchiham
gchiham Rank 1 - Community Starter
edited Aug 13, 2024 7:08PM in Oracle Analytics Cloud and Server

Hello I have the following code. It works OK but i need to setup the date manually not to get CURRENT DATE.

The date I want to setup is Oct-11-2015

Can you please help me with the Syntax.

What this does Is to make Buckets:

0-30 days

31-60

61-90

91-120

121+ days

so that calculation is made CURRENT DAY (minus) ABAY Start Date

I want to do it from October-11-2015 (minus) ABAY Start Date


This is the formula


CASE
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,
  CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,
  CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) )
  || RIGHT ( '0' || CAST ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 )
  || '11' , 'YYYYMMDD' ) AS DATE ) ) > 120
THEN '121+ Days'
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,
  CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,
  CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) )
  || RIGHT ( '0' || CAST ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 )
  || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 91 AND 120
THEN '91-120 Days'
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,
  CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,
  CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) )
  || RIGHT ( '0' || CAST ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 )
  || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 61 AND 90
THEN '61-90 Days'
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,
  CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,
  CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) )
  || RIGHT ( '0' || CAST ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 )
  || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 31 AND 60
THEN '31-60 Days'
WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,
  CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,
  CAST ( YEAR ( CURRENT_DATE ) AS VARCHAR ( 4 ) )
  || RIGHT ( '0' || CAST ( MONTH ( CURRENT_DATE ) AS VARCHAR ( 2 ) ) , 2 )
  || '11' , 'YYYYMMDD' ) AS DATE ) ) < 31
THEN '0-30 Days'
ELSE '0-30 Days'
END

Answers

  • plyskovich
    plyskovich Rank 3 - Community Apprentice

    Hi gchiham,

    you can use presentation variable for example @{pv_date}. You can create a dashboard with prompt and try to pass a value to the variable.

    1. CASE 
    2. WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" , 
    3.   CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR , 
    4.   CAST ( YEAR ( @{pv_date}) AS VARCHAR ( 4 ) ) 
    5.   || RIGHT ( '0' || CAST ( MONTH ( @{pv_date}) AS VARCHAR ( 2 ) ) , 2 ) 
  • gchiham
    gchiham Rank 1 - Community Starter

    Hi Pavel

    What your are giving me is exactly what i wanted. You are awesome. This is my first time using PV. Can you tell me is I am doing the correct steps please.

    Step 1.

    Added the formula with the @{pv_date}

    CASE  
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,  
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,  
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )  
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )  
    || '11' , 'YYYYMMDD' ) AS DATE ) ) > 120  
  THEN '121+ Days'  
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,  
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,  
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )  
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )  
    || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 91 AND 120  
  THEN '91-120 Days'  
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,  
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,  
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )  
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )  
    || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 61 AND 90  
  THEN '61-90 Days'  
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,  
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,  
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )  
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )  
    || '11' , 'YYYYMMDD' ) AS DATE ) ) BETWEEN 31 AND 60  
  THEN '31-60 Days'  
  WHEN TIMESTAMPDIFF ( SQL_TSI_DAY , "Employee"."ABAY Start Date" ,  
    CAST ( Evaluate ( 'TO_DATE(%1,%2)' AS CHAR ,  
    CAST ( YEAR ( @{PV_DATE} ) AS VARCHAR ( 4 ) )  
    || RIGHT ( '0' || CAST ( MONTH ( @{PV_DATE} ) AS VARCHAR ( 2 ) ) , 2 )  
    || '11' , 'YYYYMMDD' ) AS DATE ) ) < 31  
  THEN '0-30 Days'  
  ELSE '0-30 Days'  
END 

    STEP1.png

    Step 2.

    What I am i suppose write in this section ? Insert Variable

    STEP2.jpg

    Step 3.
    Edit Prompt I have it as you see below. As you can see I just made the prompt to select a month. Will this work ?

    Sorry my ignorance but I really need this help.

    STEP3.jpg

  • plyskovich
    plyskovich Rank 3 - Community Apprentice

    Hi,

    You have done all steps correctly.

    The first step is correct ( Perhaps you should add quotes '@{pv_date}' ).

    The second step - You have added presentation variable to formula. Also, you can add default value to pv variable using this option or write youself (as you've did) for example:

    @{myFavoriteRegion}{'Central'}

    @{myFirstDate}{DATE '1973-07-24'}

    @{myFavoriteString}{'Nico'}

    @{myYear}{max(Time.Year)}

    The third step - correct.

    I suppose this guide will help you.

    OBIEE 11g Tips & Tricks: OBIEE11g: How to Use Prompt as Report Selector or Using a Prompt as a Report Selector in OBIEE1…

    Have a nice day!