Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 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
use ago, todate function dynamically
I have an alternative calendar hierarchy. One is for Gregorian calendar and another one is for fiscal calendar. The end user can report a metric using Gregorian calendar or fiscal calendar based on a session variable. I have an issue on how to decide hierarchy level based on a session variable. The following is an example. The level can be either Gregorian Week or Fiscal Week depend on a session variable. I tried to use a case statement outside of the AGO function, but it did not work as OBIEE checks formula even though the case is false.
IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Gregorian Week", 1), 0)
Thanks
Answers
-
a) What exact version?
b) Is that in the RPD or in an analysis?
c) What was your EXACT formula that failed? What did you try to substitute where and how did you try?
0 -
OBIEE 12.1.2.2
The following is the formula in the metric:
CASE WHEN VALUEOF(NQ_SESSION."LY_SHIFT") = 'GUNSHIFT' THEN
IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Gregorian Week", 1), 0)
ELSE IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Fiscal Week", 1), 0)
END
Fiscal Week and Gregorian Week are 2 different levels in alternative hierarchies. Both has the same child level which is day level.
The error message is:
State: HY000. Code: 22036. [nQSError: 22036] Level in Ago function (Fiscal Week) must be a rollup of (or equivalent to) the measure level (Gregorian Week). (HY000)
Thanks
0 -
So you're saying only the Gregorian part produces the issue? If that is so then either your Gregorian time dimension roll-up is misconfigured or you're trying to switch dynamically to the Gregorian rollup while keeping the fiscal attributes for example.
0 -
It failed in both way. It failed no matter I put fiscal week or gregorian week in the report. The error message is similar. The issue here is that when I put gregorian week on the report, OBIEE still tried to generate SQL for ELSE IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Fiscal Week", 1), 0) even the case result is false. OBIEE tried to generate the sql for both case. That caused failure. So is there any alternative way to do that? My requirement is to use Gregorian week if session variable is 'GUNSHIFT', otherwise use Fiscal Week for the ago function.
Thanks
0 -
Normally that error points to a wrong configuration of the hierarchiesor just a wrong usage
0 -
That is correct. The OBIEE tried to do AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal Calendar"."Fiscal Week", 1) even though the case result for VALUEOF(NQ_SESSION."LY_SHIFT") is false for that. That is my question. Is there anyway in OBIEE that use Fiscal Week as level in ago function when the value of a session variable is 'A' and use Gregorian Week as level when the value of the same session variable is 'B'. Gregorian week and Fiscal week are in different alternative hierarchies.
Thanks
0 -
Did you try using the CASE WHEN inside the AGO function for the value of the 2nd parameter?
So something like AGO("... Receipts Qty", CASE WHEN VALUEOF(NQ_SESSION."LY_SHIFT") = 'GUNSHIFT' THEN "Core"."Date Retail Fiscal Calendar"."Gregorian Week" ELSE "Core"."Date Retail Fiscal Calendar"."Fiscal Week" END, 1)
I don't have an OBIEE available right now to see if it's nice enough to allow something like that, but ... why not? Trying doesn't cost anything
0 -
I also tried. It threw error when I tried to close the edit form. It looks like AGO does not support this syntax.
Thanks
0 -
Yeah, I was scared of that as it reference a level and so it's more strict than just a column in the formula itself...
0 -
I can use 2 metrics internally. One is for Gregorian and one is for fiscal. That is no problem. The question is how to consolidate them, so the end user will only see 1 metric from the presentation layer.
Thanks
0
