We need to pull record based on a date prompt as we are working in HR module; All tables have Start Date and End Date, In IDT we are trying to put a Business layer filter for that we created a date dim which has Year, month,Date field(which has multiple Date values), Start Date , end date and User date(which is Null ).
Also created LOV for Date field
in the filter the formula we applied is:
User_Date = case when @prompt('Enter Date','D','DATE',,) BETWEEN Start_Date and End_Date then User_Date else '9999-01-01' end
it got validated and when tried to test it in query i am asked to enter date field but not getting any values.
Please help me out