Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 = >}
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'))"}
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 .....
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)
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]
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 = >}
Thanx - that works - in the end by explaining the problem made me realise that it was much simpler!