6 Replies Latest reply on Dec 20, 2012 10:30 PM by SPowell42

# Last Year YTD in obiee11g

Hello Experts

I need to create a report obiee11g with 3 columns, Selected Month, YTD, and YTD Last year.

So, if the user select 2012/Apr( in a Yr-Mon prompt), the first column shows the sum of the sales for that month, the second column the sum from 2012/Jan to 2012/Apr; And the Third Column the sum of 2011/Jan to 2011/Apr .

Sales for the Month , Actual YTD , they are getting calculated OK but Last year YTD is not coming properly . It is calculating the sum from 2011/Jan to 2011/Dec and not till 2011/Apr.

I am using the following logic to calculate these fields:

Previous_Actual_Ytd = AGO("TD poc"."Fact"."ACTUAL QTD", "TD poc"."TimeDim"."Year" , 1)
ACTUAL YTD = TODATE("TD poc"."Fact"."ACTUAL", "TD poc"."TimeDim"."Quarter"

Pls. guide.

Sample Data : (DAY ID is linked to Date ).

YEAR_ID     QUARTER_ID MONTH_IDDAY_ID     ACTUAL
1     11     111     1     100.0
1     11     111     2     200.0
1     11     111     3     500.0
1     11     111     4     800.0
1     11     222     5     1000.0
1     11     222     6     3000.0
1     11     222     7     300.0
1     11     222     8     1200.0
1     11     333     9     1800.0
1     11     333     10     2200.0
1     11     333     11     5000.0
1     11     333     12     400.0
1     22     444     13     900.0
1     22     444     14     6600.0
1     22     555     15     5800.0
1     22     555     16     3600.0
1     33     666     17     9600.0
2     34     777     18     1500
2     35     888     19     5000
2     35     999     20     2000
2     36     10000     21     8000
• ###### 1. Re: Last Year YTD in obiee11g
HI,
The formula which you are used for the last year YTD will calculate the period from jan/11 to dec/11.

YTD:Year-to-date:It means the sum of measure value of the complete year.If it is a current year then it will calculate to till current month.

thanks,
prasssu
• ###### 2. Re: Last Year YTD in obiee11g
I understand that but how to calculate the sum of 2011/Jan to 2011/Apr for Last yr based on the month selected from the prompt.

Any help / guidance would be highly appreciated.

Regds
• ###### 3. Re: Last Year YTD in obiee11g
This is not something you can do in the repository.

You could create a filter (calculation that is) to remove any month > your_prompt, but this will require that your month is a date or timestamp value and not a varchar equivalent, or that you convert any user friendly text dates into a date / timestamp to do this.

Make sense?

Alternatively case when sum(date < your_prompt then your_balance else 0 end)

regards,

Robert.
• ###### 4. Re: Last Year YTD in obiee11g
Hi Robert

As per your advise , I am trying to create the following script in teh fact expression. But it is not giving correct value.

CASE WHEN "Core"."Dim - Date Fiscal Calendar"."Fiscal Year Number" = VALUEOF(NQ_SESSION.CURRENT_YEAR)-1 and "Core"."Dim - Date Fiscal Calendar"."Calendar Month Number" <= VALUEOF(NQ_SESSION.CURRENT_MONTH) THEN "Core"."Fact - Fins - GL Other"."US Eq Amount"
else NULL
END

Obvioulsy I can use Request variable to dynamically chnage the values of the variables as per the user prompted value.
• ###### 5. Re: Last Year YTD in obiee11g
Hi Robert

Any help here pls.
• ###### 6. Re: Last Year YTD in obiee11g
This is where I would get tricky. In the physical layer I'd create an alias to your fact table called FACT_YEAR_AGO, and I'd join it to the time dimension using year = year - 1 or whatever is similar in your schema. Then "Year Ago" metrics would just source from the alias fact table instead of the original fact table, and "Year Ago YTD" would simply use a YTD formula against the alias table.

Hope this helps, easier to show than to describe in email.

Thanks,
Scott