## Forum Stats

• 3,872,069 Users
• 2,266,376 Discussions

Discussions

# complicated calculation

Member Posts: 500
edited Oct 13, 2009 6:05AM
here is the case.

there are 2 dimensions and a fact.

2 dimensions: market and period
fact: profit

data in the database:

!http://img26.imageshack.us/img26/8099/datai.png!

on the dashboard , there is a prompt and a table.
the prompt defaultly selected a period, says period = 200905

my expected results:

!http://img26.imageshack.us/img26/2139/resulte.png!

how to achieve my goal ? do I need to modify RPD ? if needed, it is allowed.

thank you very much!
Tagged:

• Member Posts: 554
edited Oct 13, 2009 6:05AM
You can try with this approach:

OBIEE same dimension different value measures
http://108obiee.blogspot.com/2009/07/obiee-same-dimension-different-value.html

Then you would have two date dimensions to be able to write two different filters for profit, one is normal (200905) and the second is rolling part - latest 3 periods sum and latest 3 periods average and the only problem is how to get (write) this rolling filter when you have only one month parameter as dashboard prompt.

You can use this post:
3441161

The filter applied on the rolling part is similar like:
TIMES2.CALENDAR_MONTH_DESC between EVALUATE('TO_CHAR(ADD_MONTHS(TO_DATE(%1, %2), %3),%2)' as char, '@{month}', 'yyyy-mm', -2) and '@{month}'

@{month} -> presentation variable from the dashboard prompt (you said that you have only 200905, so one month).

For the average profit create third measure in BMM (duplicate one from the SALES2, my example, and set aggregation to AVG).