Help with Formula Syntax please

Question

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

plyskovich · Answer

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!

gchiham · Answer

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

Step 2.

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

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.

plyskovich · Answer

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.

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