Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

select one value from between date dashboard prompt

Received Response
153
Views
4
Comments
User_YGBH8
User_YGBH8 Rank 1 - Community Starter
edited Aug 13, 2024 5:19PM in Oracle Analytics Cloud and Server

Hello,

I have presentation variable that is build based on between dates dashboard prompt (so user chooses two dates from calendar), I can assign only one variable to this prompt. Then I am passing this presentation variable to column filter, this works fine however two values are being referenced whereas I only want the upper date to be passed to filter. Is this possible ?

Regards.

Answers

  • User_YGBH8
    User_YGBH8 Rank 1 - Community Starter

    I have ended up in such BI sql clause "DIM - CDATE.DATE" =  last(date '2019-12-01',date '2020-01-29')), how from such two values bracket list select only second one using BI SQL ?

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    The two dates get passed as a string that looks like this: 2019-12-31,2020-01-29

    This filter parses the second date using the position of the comma, then concatenates it into: date '2020-01-29'

    Then casts it as date.

    "Project Schedule Milestones"."Advertise Actual"<=CAST('date '''||SUBSTRING('@{dashboard.variables['varDates']}' FROM POSITION(',' IN '@{dashboard.variables['varDates']}')+1 FOR 20)||'''' AS DATE)

    pastedImage_2.png

    pastedImage_1.png

  • User_YGBH8
    User_YGBH8 Rank 1 - Community Starter

    This is amazing, I am so thankful - works like a charm.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Glad I could help!

    That being the case, would you mark my answer as "correct", so others with the same question will see it?

    Jerry