Skip to Content
0

Calculations Based on Parameter

Apr 26, 2017 at 02:46 PM

64

avatar image
Former Member

Hello All,

I have fields called ‘Posting Date’ and ‘Revenue’.

There is one selection parameter (to be filled in by users) on ‘Posting date’ (single value).

Based on this date I need to restrict ‘Revenue’ value in a given column.

I mean if the date entered is between 1st Oct 2016 to 31st Dec 2016, column should show Revenue for posting period ‘Oct 2016’ (i.e. Oct data of same year mentioned in selection).

And if date entered is between 1st Jan 2017 to 30th Sep 2017 (i.e. any date other than above range) the column should show revenue for posting period Oct 2016 (i.e. Oct data of previous year).

I mean Column month is fixed i.e. Oct but year depends on value entered as expalained above. Kindly suggest.

Thanks.

Naoman D

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Dell Stinnett-Christy Apr 26, 2017 at 05:31 PM
0

The best way to do this is going to be in the in the Select Expert unless you need data outside of the October range. What type of database are you connecting to and what is the format and name of the Posting Period field?

-Dell

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

SAP ECC tables --> BO universe

Field format is mm-dd-yyyy

0

You'll manually edit the formula in the Select Expert to include something like this:

(
  (
    Month({?Posting Date}) >= 10 and Month({Mytable.PostingPeriod}) = 10 and 
    Year({Mytable.PostingPeriod})) = Year ({?Posting Date}) 
  ) 
OR
  (
    Month({?Posting date}) < 10 and Month({Mytable.PostingPeriod}) = 10 and 
    Year({Mytable.PostingPeriod})) = Year ({?Posting Date}) - 1
  )
)

Put an "and" before this if you're adding it to an existing filter. Note that the parentheses are required for getting this to work correctly.

-Dell

0
avatar image
Former Member Apr 26, 2017 at 05:10 PM
0

Please suggest

Share
10 |10000 characters needed characters left characters exceeded