Oracle Communities

Oracle Analytics and AI

Child Item

Previous quarter value in variable

3612030

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

Find more posts tagged with

Accepted answers

All comments

Robert Angel

AND????

Are you sure the two are true in combination??

3612030

Well, yes, they should be. First is the passed variable @Quarter and the second should just be using the previously passed variable.

Robert Angel

But can I repeat my question.

On a row of data are you certain that both parts of the AND statement can be true at the same time?

[Deleted User]

3612030 wrote:"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

What in the name of everything holy, unholy and in between?! Why do things easy when they can be made complicated X-D

Jerry Casey

I don't see any single quotes that should enclose this character value in the SQL filter, in either of the versions you show. But that would throw an error, not "No Results".

Thomas Dodds

Forcing the query engine to write bad/inefficient SQL to boot ...

[Deleted User]

Yep. Basically like pluggin in a microwave you modified into your car to override its engine management software.

Robert Angel

On my last question, can you prove this for me; -

"But can I repeat my question.

On a row of data are you certain that both parts of the AND statement can be true at the same time?"

Create explicit filters that would create the equivalent of what you are trying to achieve with the date based filter - see if it gives you any data??

If not then look into using filter functionality to achieve your desired result, accessed via the function palette.

3612030

Robert - again didnt worked, still no results.

Christian, Thomas - I would really appreciate to get this "simple" solution since you already commented my case.

This SQL is issued that way because the column with Quarter IS NOT A DATE and even if casted to, the format would still be "2018 Q 2" and that still I CANNOT use to make a calculations! Remember, Im using prompt presentation variable and I have to pass this to second - Historical - Subject Area.

For example, using your suggestion about AGO function, this didnt work for me because of "Unresolved level" error - AGO("Historical Pipeline Facts"."Closed Opportunity Revenue", "@{QUARTER}", 1)

In fact everything that I include in this function throws me this error, no matter the column.

I would really appreciate a much simpler solution to this problem

Robert Angel

And did you try my test with two explicit filters to ensure that the data in combination (in the same row) actually does exist?