Expand selection in set analysis

[#1]

Hi,

I'm trying to have a chart that displays the sales for the selected and the five prior months.

I have created two varibles that selects the first month I want to display, for example, if I select 5 the varible holds the number 12. The second varible holds the year for the first month I want to display.

The problem is that I dont know how to tell QV to select the dates in between the selected month and the month that the varible is holding.

Example: If I select Year 2011 and month 5 I want the chart to look like this:

Year Month Sales

2010 12 10

2011 1 15

2011 2 25

2011 3 30

2011 4 10

2011 5 50

I have included a sample file.

Maybe I'm making this a bit complicated for myself? 🙂

[stephencredmond · Accepted solution]

My suggestion is for you to add a new field to hold the date of the month (I used MakeDate(Year, Month) to create a date representing the 1st of the month). Use this field in your set and then you can manipulate it easier using AddMonths.

=Sum ({$<Month=,Year=,MonthDate={">$(=AddMonths(max(MonthDate),-6))<=$(=max(MonthDate))"}>}Sales)

Regards,

Stephen

[#3]

Thank you so much for your fast reply!

I will try this first thing thursday when I get back to work.

Again, thanks alot!

[#4]

It works great, thanks alot!