nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to do MAX Date Logic less than Prompted AsOfDate in Analysis

Brenda Jacks-49721

Summary

Trying to get the maximum Salary Change Date in OTBI Analysis

Content

I am trying to convert a filter to SQL to get the maximum Salary Change Date that is less than or equal to a prompted AsOfDate in an OTBI Analysis. Here is the filter I am using:

"Compensation - Salary History Details Real Time"."Salary Details"."Salary change date" =
SELECT MAX("Compensation - Salary History Details Real Time"."Salary Details"."Salary change date")
FROM "Compensation - Salary History Details Real Time"
WHERE "Compensation - Salary History Details Real Time"."Salary Details"."Salary change date" <= '@{AsOfDate}{@{system.currentTime}[YYYY-MM-DD]}'

I'm getting error getting drill information and wondering how I can fix this? I have attached the catalog.

Thank you!

Brenda

Test Assignment Data WITH Salary.catalog

Find more posts tagged with

SQL

Answers

OTBI

Customizing Reports

Analyses

Comments

rashid ramzan-45341

First: What is the error that you are getting?

Second: By setting VARIABLE PARAM_EFFECTIVE_DATE to AsOfDate it will return the latest salary change record on or before that date so I don't think you need any Max

If your error is related to Date Format then try MM/DD/YYYY in your prompt. Thanks

Regards,

Rashid