Set Analysis and Dates

[alexis]

Hi,

I need to build functionality that enables the user to select one or more consecutive months (say June and July 2012) and we need to present 2 sets of results, one for the selected months (June and July 2012) and another that we can "Previous Year" that shows the results for the same period in the previous year (June and July 2011 in this example)

For my dataset I created a Master Calendar that includes a field called MonthID which is the month number staring from "1" for the oldest month and incremented by 1 for every month after that, e.g.

Date	MonthID
1/1/2006	1
10/2/2006	2
11/3/2006	3
	etc
1/6/2011	54
1/6/2012	66
1/7/2012	67
1/12/2012	72

I am using Set Analysis to calculate the the "PreviousYear"  which works for almost all scenaria except in the case where a December (Any December) is the month selected (or the minimum of the months selected (e.g. December 2012 and January 2013)

[code]

{$<MonthID = {">=$(=min(MonthID) - 12)"},

Date = {"<$(=Date(MakeDate(Year(Max(Date))-1, Month(Max(Date))+1, 1),'DD/MM/YYYY'))"},

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

[/code]

To see how the above works, assume that June and July 2012 was chosen:

The above set analysis will generate:

MonthID >= min(MonthID) -12

which is

MonthID >=66-12

which is

MonthID>=54

Also, the 2nd line of my set analysis generates:

Date < MakeDate(2011, 8, 1)

which is

Date < 1/8/2011

so to get the data for "PreviousYear" for the selected range of June and July 2012:

MonthID >= 54

Date < 1/8/2011

PROBLEM:

If the user selects December 2012

MonthID >= min(MonthID) -12

which is

MonthID >=72-12

which is

MonthID>=60

and

Date < MakeDate(2011, 13, 1)

which is

an invalid date as there is no month 13!!!!

Can anyone help me get 1/1/2012 and the maximum date for the above so that the data set will be based on:

MonthID >= 60

Date < 1/1/2012

[swuehl]

Instead of using a MonthID, I think you should be able to use only an advanced search in Date field, using an lower as well as an upper limit.

But coming back to your initial request, try something like

Date = {"<$(=Date(addmonths(monthstart(Max(Date)),-11),'DD/MM/YYYY'))"}

[alexis]

Hi swuehl,

Your suggestion was very helpful indeed as it calculates correctly the "Date" part as 1/1/2012 in the case described above.

That alone however does not generate the right result because it does not have a lower limit which was the reason I introduced the MonthID concept.

Just so that you have the complete story, the formula in question is stored in a variable (say "vSetPreviousYear)) and in my chart I simply say:

= Sum($(vSetPreviousYear) Sales)

Can you assist to complete the formula to cater for the correct range .....

[#4]

Alexis,

Hi,

Please check

Month(Max(Date))+1 will not be 13. it will be next month to the Max(Date).

IF Max of date is 'dec' then Month(Max(Date))+1 will be Jan.

(I.e) MakeDate(2012,month(12)+1,1)

[alexis]

Hi Sampath,

Thank you for responding.

I think it's my fault for not explaining the exact requirement.

If the user selects the range:

June2012 - August 2012 then the result should be all data for the range

June 2011 - August 2011

Likewise, if they select:

Dec 2012

then the resultset should comprisedata for

Dec 2011.

The previous respondent dealt with the issue of a December selection where my formula:

Date = {"<$(=Date(MakeDate(Year(Max(Date))-1, Month(Max(Date))+1,

was flawed because in the case of December it was attempting to calculate Date <= 1/13/yyyyy

As you rightly state there is no such thing as month 13!!

swuehl's response takes care of the upper limit for the data set - using his suggestion on its own:

Date = {"<$(=Date(addmonths(monthstart(Max(Date)),-11),'DD/MM/YYYY'))"}

will return (in the case of a December 2012 selection for example) data that meets the criterion:

data < 1/1/2012

This is incomplete as we are interested in:

1/12/2011 <= data < 1/1/2012

What we are missing is the lower limit of the dataset and that is why, in my original formula I introduced the concept of MonthID (see my original post).

To be honest, the simplest way to resolve this is to finetune my original definition (see below) and just base the selection by extending the MonthID line to read:

Month ID >=min(MonthID)-12

AND

MonthID <max(MonthID)-11

(I am missing the part in bold above)..

Can someone help?

Alexis

[code]

{$<MonthID = {">=$(=min(MonthID) - 12)"},

Date = {"<$(=Date(MakeDate(Year(Max(Date))-1, Month(Max(Date))+1, 1),'DD/MM/YYYY'))"},

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

[/code]

[Gysbert_Wassenaar · Accepted solution]

If you want to show only a single value as result and not results per month then try this:

{$<MonthID = {">=$(=min(MonthID) - 12)<=$(=max(MonthID) - 12)"},

Date =,

Year = ,

Quarter = ,

Period = ,

[Period (#)] = ,

Month = >}

[alexis]

Thanx - that works - in the end by explaining the problem made me realise that it was much simpler!