Forum Stats

  • 3,872,069 Users
  • 2,266,376 Discussions
  • 7,911,043 Comments

Discussions

complicated calculation

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:

Answers

  • Goran Ocko
    Goran Ocko 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).

    Instead of yyyy-mm put your month format, yyyymm (200905).

    Regards
    Goran
    http://108obiee.blogspot.com
  • 707781
    707781 Member Posts: 500
    thank you very much!

    let me try!
This discussion has been closed.