Calculations Based on Parameter

[former_member364727]

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 1^st Oct 2016 to 31^st 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 1^st Jan 2017 to 30^th 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

[DellSC · Accepted solution]

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

[former_member364727 · Accepted solution]

SAP ECC tables --> BO universe

Field format is mm-dd-yyyy

[DellSC · Accepted solution]

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

[former_member364727]

Please suggest