Skip to Content
avatar image
Former Member

Calculations Based on Parameter

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 26, 2017 at 05:31 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

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

    Please suggest

    Add comment
    10|10000 characters needed characters exceeded