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

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