Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 214 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Previous quarter value in variable
Answers
-
Btw - advising us up front that this was OTBI would have yielded better advice sooner, this is why in the how to ask questions they ask you to put your version of the software on the post.
0 -
Ok, so now without any general filters, only using filters inside columns:
1. FILTER("Historical Pipeline Facts"."Open Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = (CASE WHEN RIGHT('@{QUARTER}', 1) = '1' THEN CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) -1 AS CHAR)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('@{QUARTER}',1) AS INTEGER)-1 AS CHAR) END)))
2. FILTER("Historical Pipeline Facts"."Closed Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = (CASE WHEN RIGHT('@{QUARTER}', 1) = '1' THEN CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) -1 AS CHAR)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('@{QUARTER}', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('@{QUARTER}',1) AS INTEGER)-1 AS CHAR) END)))
I got a "results" but empty (calculated column shows a result of my Quarter filter (I chose 2018 Q 3) and in that form it it passed to columns):
My data is not missing since without filters I get:
0 -
Dear User,
If you are still having troubles, can you please post out put of SQL Query, including your columns "Pipeline Snapshot Enterprise Quarter" and "Enterprise Quarter". The condition will be to show valid record for any Quarter (I'm expecting output something similar to 2018 Q2 and 2018 Q3 respectively).
The reason for this is to verify if you have data for the condition you had posted earlier as below:
If you have valid data here, you will get the solutions working.
0 -
Okay, do a replace on the above just to prove it is catching the prompt please; -
FILTER("Historical Pipeline Facts"."Open Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = (CASE WHEN RIGHT('2018 Q 2', 1) = '1' THEN CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) -1 AS CHAR)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR) END)))
FILTER("Historical Pipeline Facts"."Closed Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = (CASE WHEN RIGHT('2018 Q 2', 1) = '1' THEN CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) -1 AS CHAR)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR) END)))
If those don't work then try these please (I am trying to ascertain the point of failure)
FILTER("Historical Pipeline Facts"."Open Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = '2018 Q 2'))
FILTER("Historical Pipeline Facts"."Closed Opportunity Revenue" USING ("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" = '2018 Q 2'))
Try both again without an external filter as you put it.
0 -
The first one doesnt work, the second one works. What a strange case that is...
0 -
So 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)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR) 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)||' '||'Q'||' '||'4' ELSE CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) AS CHAR)||' '||'Q'||' '||CAST(CAST(RIGHT('2018 Q 2',1) AS INTEGER)-1 AS CHAR) END)))
0 -
Nope, the trim function still doesnt change anything.
0 -
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)))
0 -
Propably sad to hear that, but it doesnt work either.
0 -
Can you put one of the formulas through length and see if it differs from if you put your quarter through length?
0
