Set analysis

[#1]

I have a report that that has past actual sales and future forecast data. I need help with a set analysis to create a field in a straight chart. Currently, the chart shows the future forecast with week numbers.... (i.e 201436, 201437, 201438 and so on)

I want to create one field that shows the sales associated with the same week from the previous year and another field that shows the sales from two years back from current year. So if I select 201436 through 201452, I want column 1 to show actual sales for 2013 from week 36 through 52. I want column 2 to show actual sales for 2012 from week 36 through 52.

yr/wk	product	region	forecast	column1 (sales from 1 year previous)	column2 (sales from 2 year previous
201436	a	west	200	194	165
201437	b	east	400	345	300

so column1 is 'actual sales' from year 2013 for weeks 36 and 37. column2 is 'actual sales from year 2012 for weeks 36 and 37

[JonnyPoole]

Take a look at the attached

I use a series of variable as follows

vMaxSelectedDate =   max(Date)   ---> most recent date in user's selections

vMinSelectedDate =   min(Date)   ---> oldest date in user's selections

vMaxSelectedDate1YearAgo =  AddYears( max(Date),-1)   ->   Most recent date in user's selection minus 1 whole year

vMinSelectedDate1YearAgo =  AddYears( min(Date),-1)   ->  oldest date in user's selection minus 1 whole year

vMaxSelectedDate2YearsAgo =  AddYears( max(Date),-2)   ->   Most recent date in user's selection minus 2 whole years

vMinSelectedDate2YearsAgo =  AddYears( min(Date),-2)   ->  oldest date in user's selection minus 2 whole years

Then the expressions to grab the date range from a year prior and 2 years prior becomes straight forward

Previous Year Sales:

sum(  {$< Year=,MonthName,Month=, Date= {'>= $(=num(vMinSelectedDate2YearsAgo))<=$(=num(vMaxSelectedDate2YearsAgo))'}>}  Sales)

2 Years ago Sales:

sum(  {$< Year=,MonthName,Month=, Date= {'>= $(=num(vMinSelectedDate2YearsAgo))<=$(=num(vMaxSelectedDate2YearsAgo))'}>}  Sales)