Sum of amount for last 6 Months from particular date within table

[veldetta]

My sample data/table looks like such:

Month	Company	Promise	Actual
Jan-15	ABC		$10
Feb-15	ABC		$20
Mar-15	ABC		$30
Apr-15	ABC		$40
May-15	ABC		$50
Jun-15	ABC		$60
Jul-15	ABC		$70
Aug-15	ABC	$50	$80
Sep-15	ABC		$0
Oct-15	ABC		$0
Nov-15	ABC		$0
Dec-15	ABC		$0
Jan-16	XYZ		$10
Feb-16	XYZ		$20
Mar-16	XYZ		$30
Apr-16	XYZ		$40
May-16	XYZ		$50
Jun-16	XYZ	$40	$60
Jul-16	XYZ		$70
Aug-16	XYZ		$80
Sep-16	XYZ		$0
Oct-16	XYZ		$0
Nov-16	XYZ		$0
Dec-16	XYZ		$0

I would like to calculate the the sum of the amount for the past 6 months from the date of the promise for each company. 

So for Company 'ABC', I would like to show $300 and for Company 'XYZ', I would like to show $210.. either by adding a new column or performing a set analysis in a chart.  I've spent a few hours trying to figure this out and i haven't gotten far. 

Thanks guys.

Regards,

Vel

[hirenjadiya]

Hi,

Looking at the data it seems for Company 'ABC' the total should be $330 instead of $300

(

15-Mar	ABC		$30
15-Apr	ABC		$40
15-May	ABC		$50
15-Jun	ABC		$60
15-Jul	ABC		$70
15-Aug	ABC	$50	$80

)

CORRECT?

[Anil_Babu_Samineni]

LET vDate = '20-01-2016'

LET vParticulatrDate = Date(monthstart(Makedate('$(vDate)',1),-6))

Here, Date should be your fixed date.

Sum({<Comapny = {'ABC'}, Date = {'$(vParticulatrDate)}>} Amount)

[veldetta]

Correct!  my error...

[veldetta]

Thanks... this helps somewhat.  However, I will need vDate to be dynamic as each company would have a different vDate.

[rupamjyotidas]

Maybe Something like this

if(Len(Promise)>0 ,RangeSum(Above(SUm(Actual),0,6)))

[veldetta]

I would like for my output to be:

Company	Promise	Actual (6 months)
ABC	$50	$330
XYZ	$40	$210

[veldetta]

I like this but I will need to use date as there could be more than 2 entries for in a month and I need to capture those as well.

[rupamjyotidas]

Something like this

Sum({<Month={">date(AddMonths(Month1,-6),'MMM-YY') <=date(AddMonths(Month1,0),'MMM-YY')"}>} Actual)

Where Month1 is

Date(If(Len(Promise)>0,Month),'MMM-YY') as Month1