Categories
- All Categories
- Oracle Analytics Learning Hub
- 19 Oracle Analytics Sharing Center
- 18 Oracle Analytics Lounge
- 232 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.9K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 87 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Previous quarter value in variable
Answers
-
Is the answer to both; - 8??
0 -
I need closure, did you check, are both exactly the same?
0 -
On quarter column is 8 but on my calculation is 34... strange
0 -
Okay, strange agreed, can you find out what the ascii of a / the characters after the 8th is?
0 -
It can be seen in CSV after export. There is a huge gap between "2018" and "Q1":
0 -
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.
0 -
34 is the default length of CHAR. Perhaps the statement should be using CAST AS CHAR(4) and CAST AS CHAR(1) where appropriate?
0 -
Yes, I posted that previously?
0 -
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??
0 -
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.
0
