Calculating Amount between two time periods Syntax help

[#1]

Hi,

I have a pivot chart with Product, Contract Number and Contract End Date listed on the Y Axis (rows) and months (Jan 2013, Feb 2013 ... Dec 2014) on the X Axis (Columns). Contracted amount by month (sum(Amount)) is the expression.

So the chart looks like as shown below

Product   ContractNo. ContractEndDate YearMonth Jan 2013 Feb 2013 Mar 2013 .....Dec 2014

Prod A     000001         10/31/2015                              $100         $200         $50  .....      $133

Prod B     000005           4/30/2014                                $50          $75         $60  .....        $20

If we assume the Closed Month = August 2013, I want to create a calculation (Total Remaining Amount) (a column next to Contract End Date)  that sums up all amounts From Sep 2013 upto 10/31/2015 for Prod A and  upto 4/30/2014 for Prod B

The calculation that I need help with the syntax in words is

Sum All Amounts between Sep 2013 (i.e. ClosedMonth +1) and Contract End Date

Any help will be greatly appreciated.

Thanks,

Amitesh

[jpapador]

Try this as your expression:

Sum(If(YearMonth >= Month(Today()) and YearMonth <= (Month(ContractEndDate)+1), Amount))

[#3]

Hi Jpapador,

Thanks for your response. the trouble with that expression is that it compares month with a year and month combination and returns everything under the sun.

I tried something like that myself before posting the question as given below

sum(

if(Month>= (Month($(vVersionMonthEnd)) +1) AND Month(YearMonth)<=Month(ContractEndDate) ,Amount))

where vVersionMonthEnd returns the version closed date like 2013/08/31, column Month returns Jan Feb Mar...

The trouble with that expression is that when I put it in a straight chart to view values by month, values for months Jan- Aug for each year is stripped off, when I want those months to be stripped off only for the current year.

the trick here is successfully comparing year and month from the YearMonth   column which has values like 2013-01, 2013-02 and so on.

That is the expression help I am looking for.