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
Hello!
How to do a proper calculation to get a previous quarter value using passed presentation variable? I have @{Quarter} in my prompt which passes a quarter for "Sales - CRM Pipeline"."Time".Enterprise Quarter" and then using this variable I want to get a previous quarter for
"Sales - CRM Historical Pipeline"."Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter". And for this im using:
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
and it gives me a proper calculated result (for example 2017 Q4 if @QUARTER is 2018 Q1)
But then when I try to use it in my filter like that:
"Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" IN (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
The analysis gives me no results. And I have data in snapshots for many quarters so I dont know why it behaves like this....
Will aprreciate help
Alex
Answers
-
If you have a well formed time dimension that has a corresponding logical dimensions with levels assigned, then you can easily achieve this by using the AGO time series function.
0 -
Ok I know, but there are many other details that prevents me from using this method. Mainly because I have to prompt for a Quarter and I have to see a data from that Quarter and historical data from quarter -1. And I dont know if function ago can support this.
It has to be done through a presentation variable. Do you have any solution what can be done in that case?
0 -
Amen to Joel's comment.
But on your question - you seem to be missing a right parenthesis; -
"Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter" IN (....................... ) END - there should be a bracket after that end statement.
Also, in your example you show no space between Q and the number; - 2018 Q1; but in your syntax you concatenate a space in; -
|'Q'||' '||'4'
0 -
Im sorry Robert, right bracket exists, I just omitted it here accidentaly...
Also all the values are like "2018 Q 1" so my spaces are correct, again I just mistyped it here
Here is the proof that data are correct:
0 -
I don't see why this is a problem. All you need to do is to pass the Quarter entered in the dashboard prompt into a presentation variable and then calculate the previous quarter using the AGO function .
0 -
Okay, if you have confirmed that your function returns exactly the same as your Quarters then I would change your IN to an equal sign; =
0 -
Probably also worth checking that your 2017 Q 4 is exactly what you see, check the length, just in case there is something you cannot see there.
You might find it easier to strip the text and spaces out of it, then you can just take it away without all that cast and case.
0 -
Robert - unfortunately this doesnt change anything...
Joel - can you give me an example of ago function with a presentation variable? Just remember that prompted quarted is from one Subject Area and the quarter ago is from Historical Pipeline.
0 -
Try changing your filter to SQL and; -
cast(replace(replace("Pipeline Snapshot Date"."Pipeline Snapshot Enterprise Quarter","Q","")," ","") as integer) = (cast(replace(replace('@{QUARTER}',"Q2","")," ","") as integer) -1)
Cold coded - may need minor syntax fixes...
0 -
One more photo of the issued SQL:
0
