use ago, todate function dynamically

User729543-Oracle

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

[Deleted User]

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?

User729543-Oracle

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

[Deleted User]

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.

User729543-Oracle

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

[Deleted User]

Normally that error points to a wrong configuration of the hierarchiesor just a wrong usage

User729543-Oracle

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

Gianni Ceresa

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

User729543-Oracle

I also tried. It threw error when I tried to close the edit form. It looks like AGO does not support this syntax.

Thanks

Gianni Ceresa

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...

User729543-Oracle

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