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

Previous quarter value in variable

Received Response
233
Views
54
Comments
1235

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Is the answer to both; - 8??

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    I need closure, did you check, are both exactly the same?

  • 3612030
    3612030 Rank 3 - Community Apprentice

    On quarter column is 8 but on my calculation is 34... strange

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Okay, strange agreed, can you find out what the ascii of a / the characters after the 8th is?

  • 3612030
    3612030 Rank 3 - Community Apprentice

    It can be seen in CSV after export. There is a huge gap between "2018" and "Q1":

    155.JPG

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Csv??

    What csv??

    It looks like you just put a substring around the year part of the formula result then, must be something weird in casting an integer to a char(4)....

    Then it should be job done.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    34 is the default length of CHAR.  Perhaps the statement should be using CAST AS CHAR(4) and CAST AS CHAR(1) where appropriate?

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Yes, I posted that previously?

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    This was the previously;-

    "Okay, try this.

    FILTER("Historical Pipeline Facts"."Open Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = trim(CASE WHEN  RIGHT('2018 Q 2', 1) = '1'  THEN CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) -1 AS CHAR(4))||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR(4))||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR(1)) END)))

    FILTER("Historical Pipeline Facts"."Closed Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = trim(CASE WHEN  RIGHT('2018 Q 2', 1) = '1'  THEN CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) -1 AS CHAR(4))||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR(4))||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR(1)) END)))"

    You said it doesn't work??

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I can't work with the OP's data, but I was looking at the syntax of his original filter.

    I found that this statement will run if I append it to the filter of an unrelated query:
    '2018 Q 1'=(CASE WHEN RIGHT('2018 Q 2', 1) = '1' THEN CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) -1 AS CHAR(4))||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR(4))||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR(1)) END)

    But I get "No Results" if I remove the CHAR lengths.