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

Filter Period depending on CurrentMonth

Received Response
223
Views
5
Comments
User_KJI1O
User_KJI1O Rank 2 - Community Beginner
edited Aug 13, 2024 6:10PM in Oracle Analytics Cloud and Server

I am trying to create an Analysis. I am trying to use SQL Expression and using Below Query but this does not work. My requirement is to filter Period.So if Current Month is Jul then Filter should be on Jul if its Aug then filter should be on Jul,Aug and If its Sep then Filter on Jul,Aug,Sep and so on. Can someone help to see why my below query does not work.

SELECT CASE CAST(VALUEOF("QFBI203:CurrentMonthAlias") AS CHAR(3)) WHEN 'Jul' THEN 'Jul' WHEN 'Aug' THEN 'Jul';'Aug'

WHEN 'Sep' THEN 'Jul';'Aug';'Sep'

WHEN 'Oct' THEN 'Jul';'Aug';'Sep';'Oct'

WHEN 'Nov' THEN 'Jul';'Aug';'Sep';'Oct';'Nov'

END From "Prompts"

Appriciate if someone can help.

Answers

  • asim cholas
    asim cholas Rank 6 - Analytics Lead


    Ur requirement not seems to be very clear. Why cant you add the Month(date) column with >= 7 and Month(date)<= Month(CURRENT_DATE)

    ?

  • Gianni Ceresa
    Gianni Ceresa Mod

    Asim already went on a better way to do that. Ideally your time dimension has the required columns to not have to deal with textual months matching.

    Other than that you say you want to filter but post a SELECT ... FROM piece of code, not really sure what you really try to do.

    (A filter is a condition in a WHERE statement in SQL, so with your SELECT ... FROM it's quite confusing).

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Not sure what you want,  Gianni is right, you should use the time dimension column and not deal with textual months. Also the syntax its not right you are using ";" to give two values. I am not sure if you could give more than two values for CASE statement. If you describe in more detail, could be there a better path to solve your question.

    However, if you REALLY BUT REALLY, want to do that:

    -Make a SQL query with the logic of your CASE STATEMENT,  this is a sample of the query is in Oracle Database:

    select MONTH_DES month

    from

    (SELECT

        ADD_MONTHS(TRUNC( sd, 'MONTH'  ), rn) time_id

        ,TO_CHAR(ADD_MONTHS(TRUNC( sd, 'MONTH'  ), rn),'Mon')  MONTH_DES

        ,TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC( sd, 'MONTH'  ), rn),'RRRRMM')) MONTH_ID

    FROM

      (

        SELECT

          TO_DATE( '12/31/2015', 'MM/DD/YYYY' ) sd,

          rownum rn

        FROM dual

          CONNECT BY level <= 12

      ) )

    where 1=1

    and  MONTH_ID  <=  TO_NUMBER(to_char(SYSDATE,'RRRRMM') )

    and  MONTH_ID  >= 

    (SELECT CASE

    WHEN to_char(SYSDATE,'MON') = 'NOV'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -4

    WHEN to_char(SYSDATE,'MON') = 'OCT'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -3

    WHEN to_char(SYSDATE,'MON') = 'SEP'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -2

    WHEN to_char(SYSDATE,'MON') = 'AUG'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -1

    WHEN to_char(SYSDATE,'MON') = 'JUL'  THEN  TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -0

    ELSE TO_NUMBER(to_char(SYSDATE,'RRRRMM') ) -1 END

    FROM DUAL)

    group by MONTH_DES

    order by 1;

    Screen Shot 2016-11-22 at 11.26.00 AM.png

    -Check the nls_language setting

    alter session set nls_language ='ENGLISH'

    Screen Shot 2016-11-22 at 11.13.44 AM.png

    - And use using row-wise repository variables,  OBIEE - (Row-wise|List of Values) server variable [Gerardnico]

    WHERE TABLE.MY_COLUMN = valueof(NQ_SESSION.MY_VARIABLE_NAME)

    use the variable for the filter , etc

  • User_KJI1O
    User_KJI1O Rank 2 - Community Beginner

    Sorry if I was not clear. The select query I am using is actually on Filter of type SQL Expression. Also my Months are coming from Period dimension I am not using text values. But the selection of the Period.Month Filter I want to use is dynamic and hence I am using SQL Expression. Screenshot attached below. I need a way to select months depending on the value of currentmonthAlias Repositiory variable. Hope this clarifies.

    pastedImage_0.png

  • Gianni Ceresa
    Gianni Ceresa Mod

    Ok, so ... think SQL

    In a IN condition (https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm ) you must have an expression list (a "static" list of values separated by " , " ) or a subquery.

    In your case you mixed them.

    The subquery return values in rows, so if you want your month to be "Jul", "Aug", "Sep" it must be 3 rows containing each one of the 3 values.

    You can't really use a CASE WHEN to return the expression list (not covered by the documentation and doesn't seem to work in any possible way).

    So this must give you already the answer on how to make your code working.

    But if we think at what @asim cholas first posted and the idea I was trying to express, using a proper time dimension with the various columns to allow you to make these operations. If your repository variable would be a number representing the number instead of the name (or even a date) you could use BETWEEN ... AND ... or >= AND <= to easily get what you try to do.

    And there you can easily use a CASE WHEN as one of the values of the BETWEEN or <=.