sorry for the confusion. I am only working on rpd solution for now. Any suggestion on how to write a rpd ago metric formula which can get either fiscal ago or gregorian ago based on a session variable?

Thanks for the reply. It looks like your logic is on the report side. So how to implement that inside of rpd instead of report? The requirement here is to only build one metric Revenue ago in rpd instead of 2 metrics "Revenue YAGO" or "Revenue YAGO Fiscal". That is our requirement. The single metric from rpd should behave…

The screen you provided is using 2 different metrics for gregorian LW and fiscal LW. That is no issue for me. The requirement here is to only have 1 metric and this metric should pick either gregorian week level or fiscal week level dynamically based on a session variable. Not sure if you can create a similar metric. Thanks

So did you get the same error when you put both fiscal ago and Gregorian ago in one metric by using case statement? Any suggestion on how to solve the problem? Thanks

Thanks for the reply. I did use the Gregorian week on the report. The issue here is the case statement. It would not switch when OBIEE convert it to SQL. It will convert both cases and then let DB run it. If I just put IFNULL( AGO("Core"."Fact - Retail Inventory Receipts"."Receipts Qty", "Core"."Date Retail Fiscal…

We tried before and it did not work. Basically this is the same approach as the old one. When you have gregorian week on the report, it will fail on WAGO Fiscal metric even though the case statement for that is false.

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

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

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…

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…

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",…

Hi Tom, do you mean the Canonical approach (physical approach)? It does not work for month level either as the query from OBIEE will use the same columns to group by, so you will always get the same sales value for that month no matter it is in TY or LY.

The primary key that I mentioned here is the key at month level in the calendar hierarchy. OBIEE uses that key as part of group by column in the sql. The PK in the fact table is week, not month. Sorry for the confusion.

There is no issue when the report is at week level where the data is stored. The problem is when the report is at month level. The sql will group by the primary key at month level. So if the primary key at month level is 2012||02 then the week 201205 will be always the second month of year 2012. The requirement is that the…

Hi Asim What we are trying to report is TY sales vs LY sales. The data is stored at week level and the report is at week or month level. In the result, sales in week 2012week5 should belong to month 2 if 2012 is TY and same week sales in 2012week5 should belong to month 1 if 2013 is TY and 2012 is LY. This is because 2012…

So, in this way, we will not use AGO function for last year metric. Instead the LY metric will come from the fact alias that join the date key on previous period key in the dimension. Is that right? Actually this may not work. The data is stored at week level. The report is at period level. The group by columns will be the…

Can you give it in more detail? For canonical date approach, do I need multiple dates in the fact table to join with dim on different keys? Actually what i think is if the time hierarchy used in ago can be a different time hierarchy used in the report?

We use retail 454 calendar, so a full week always belongs to a month. The challenging thing is that a week could belong to different months in a regular metric or in a last year metric (ago). I am still stuck on sequence number issue. Not sure what setting caused the feature not working. Hope the OBIEE support team can…

The year level works, but the next level (using relative sequence) did not work. Also have you worked on a scenario which a dynamic calendar is needed. For example, 2012week 5 belongs to month 2 for current year (report month sales in year 2012), but the same 2012week 5 will belong to month 1 in the last year metric…

That is correct. OBIEE keep ignoring the sequence columns that I use in the hierarchy level. Not sure what is wrong.