In my application I have Sales data and a Master Calendar (containing all the usual Day, Month, Quarter etc) that is linked to the Sales table by a field called "Date".
I present to the user a listbox of MonthIDs and based on their selection I present some "point in time" statistics like "Month to date" "year to date" etc. This is achieved through the use of variable definitions such as in the following examples:
Example 1:
I have a variable called vSetMTD whose definition is:
{$<MonthID = {$(=Max(MonthID))},
Date = {"<=$(=Max(Date))"},
Year = ,
Quarter = ,
Period = ,
[Period (#)] = ,
Month = >}
In my chart the expression would be simply:
Sum($(vSetMTD) Sales)
Example 2:
I have a variable called vSetPreviousYearMTD whose definition is:
{$<MonthID = {$(=Max(MonthID) - 12)},
Date = {"<=$(=Date(MakeDate(Max(Year) - 1, Max([Month (#)]), Day(Max(Date))),'DD/MM/YYYY'))"},
Year = {$(=Max(Year)-1)},
Quarter = ,
Period = ,
[Period (#)] = ,
Month = >}
In my chart the expression would be simply:
Sum($(vSetPreviousYearMTD) Sales)
etc....
QUESTION:
I now need to extend the functionality to allow them to choose a range of months from the MonthID listbox and for my chart to show:
a) Sales for Selected Period:
This is easy: it is simply Sum(Sales)
b) Sales for Previous Period:
This is based on the idea that if they chose 3 months (say MonthIDs 12,13,14) then the Previous Period would be MonthIDs 9, 10 and 11
I am trying to create a variable definition (as those above) which defines the following logic:
