Presentation Variable conversion to Date Format — Oracle Analytics

Oracle Analytics Cloud and Server

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

Presentation Variable conversion to Date Format

Received Response
11
Views
1
Comments
user13816768
user13816768 Rank 3 - Community Apprentice

Hi,

We have two fields on a dashboard prompt, Close Month and Release Month End Date.

The Close Month is actually a date field, but user would like to see it in 'MMM-YYYY' format, so we have changed the date field to varchar.

eg: EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER (30),"Close Month",'MON-YYYY')

The release month end date is a date field (mm/DD/yyyy)and we need to print the > value which is in 'Closed Month' prompt.

eg: if user selected Jan 2017 in Close Month, we have to show values > Jan 2017 in Release Month End prompt i.e., from Feb 2017 forward

So we have passed presentation variable to the 'Close Month' prompt 'PV1' and trying to fetch the data through sql

eg: SELECT     Release Month End date  FROM "xyz" WHERE Release Month End Date  > '@{pv1}'  ORDER BY 1

Because of varchar in the Close Month and comparing against the Date field in the Release Month End Date, the formula is not working.. we have used multiple cast and evaluate functions to change presentation variable which is in varchar to date.. but not working..

Could you please provide a better solution for this?

Thanks!

Answers

  • FTisiot
    FTisiot Rank 6 - Analytics Lead

    Not casting as VARCHAR?

    You should be able to set the date format in NQSConfig.INI file.