Hi,

I am able to create a start date and end date by using the my date field in my table and creating a Where clause where my Date field is greater than a certain date, and then my End date is where Date field is less than a certain date.

But I am unable to figure out a way to create a report where the user selects the start and end month of the report, and the report displays the values for those months or days, across different years.

Right now, my report has a fiscal year BY Field then a bunch of metrics (sums, averages) for that year.

I want the user to be able to select for example, April Start Month and then July End Month, and the report will show April-July data for each row (2015,2014,2013,etc). I`m struggling on how to apply my Start/End Where clause to be applied to multiple years basically.

This message has been edited. Last edited by: <Kathryn Henning>,

'Define file' to chop the date field up into separate YY, M & D, then code to select by M & D ignoring the year?

Based on the fact that you want a From/To option you first need parameters fields where your users will select a FrMth and a ToMth.

But you have to pay attention to the fact that they can select, per example, from November to February. So then you will not be able to say : "WHERE FileMth GE FrMth AND FileMth LE ToMth" because of the years overlap. So to avoid this situation, when you work with period parameters, I suggest you to always include the year such as FrYrMth and ToYrMth.

Second this will not solve your issue since you want to include same selected period for multiple years. Because the above point is working when "WHERE FileYrMth GE FrYrMth AND FileYrMth LE ToYrMth" is possible: FrYrMth is less that or equal ToYrMth. Example: 201411 to 201506.

Here two options to include same months period for different years:

1- Perform calculations on your FrYrMth and ToYrMth to subtract one year (or two or three) to each :

-* Assuming that your &FrYrMth and &ToYrMth parameters are defined as : YYYYMM AND total selected period is not over 12 months (you need to validate or control that)
-SET &FrYrMthP1 = &FrYrMth - 100;
-SET &ToYrMthP1 = &ToYrMth - 100;
-SET &FrYrMthP2 = &FrYrMth - 200;
-SET &ToYrMthP2 = &ToYrMth - 200;

-* Then you can have
TABLE FILE abc
...
WHERE (FileYrMth GE &FrYrMth   AND FileYrMth LE &ToYrMth)
   OR (FileYrMth GE &FrYrMthP1 AND FileYrMth LE &ToYrMthP1)
   OR (FileYrMth GE &FrYrMthP2 AND FileYrMth LE &ToYrMthP2);
END

2- Which may be the simplest if you want ALL file years to be displayed on the report is to have you WHERE clause to select only chosen months regardless of the year. Your users will have to select every months they want separately (so only one parameter with multi-select). So NO From/To selection.

TABLE FILE abc
...
WHERE FileMth EQ &Mth;
END

In conclusion I think that you need to make a decision based on the needs and the data to be displayed.
IMHO I think that two parameters FrMth and ToMth are not compatible with data display when you want all existing years displayed. It's either a single parameter multi-select month with all existing years displayed (option -2-) or two parameters FrYrMth and ToYrMth with some years displayed (option -1-).

But still, many other option may applied. The only limitation is your imagination.

If you were dealing strictly with calendar years the answer is straightforward enough:

DEFINE FILE XYZ
YEAR/YY=DATEFIELD;
MONTH/M=DATEFIELD;
END

TABLE FILE XYZ
SUM whatever
BY YEAR
ACROSS MONTH
WHERE YEAR GE 2013 AND YEAR LE 2015
AND MONTH GE 1 AND MONTH LE 3
END

This produces results for January through March for 2013, 2014 and 2015

The tricky part comes when you have fiscal years and you want to display, for example, December 2014 and January 2015. This is what Martin addressed above. By combining the year and the month you can get things to sort properly. Using the month alone would mean that December 2014 appears after January 2015.

There was a recent post here that offered a solution that involved multiplying the date by 20 (if I remember correctly). You could also do the following:

-DEFAULT &STARTYEAR=1900;
-DEFAULT &ENDYEAR=2100;
-DEFAULT &STARTMONTH=1;
-DEFAULT &ENDMONTH=12;
-SET &STARTYEARMONTH = EDIT(EDIT(&STARTYEAR) | EDIT(&STARTMONTH));
-SET &OPERATOR = IF &STARTMONTH GT &ENDMONTH THEN 'OR' ELSE 'AND';
-RUN

DEFINE FILE XYZ
YEAR/YY=DATEFIELD;
MONTH/M=DATEFIELD;
YEARMONTH/YYM=DATEFIELD;
END

TABLE FILE XYZ
SUM whatever
BY YEAR
ACROSS YEARMONTH NOPRINT
ACROSS MONTH
WHERE YEAR GE &STARTYEAR AND YEAR LE &ENDYEAR
WHERE MONTH GE &STARTMONTH &OPERATOR MONTH LE &ENDMONTH
WHERE YEARMONTH GE &STARTYEARMONTH
END

The last WHERE statement is necessary so you don't get irrelevant months before the preferred starting date. Your starting dates (years, months) can obviously be derived from your data. The -DEFAULT values I have here are just for testing. The &OPERATOR is necessary to handle situations where all the compared months fall either within (AND) or span (OR) calendar years.

There is still work to be done here, because you will want to show your values by fiscal, rather than calendar years.

This message has been edited. Last edited by: George Patton,

With Fiscal years:

-SET &STARTYEAR=2013;
-SET &ENDYEAR=2015;
-SET &STARTMONTH=12;
-SET &ENDMONTH=03;
-SET &STARTYEARMONTH = EDIT(EDIT(&STARTYEAR) | EDIT(&STARTMONTH));
-SET &OPERATOR = IF &STARTMONTH GT &ENDMONTH THEN 'OR' ELSE 'AND';
-RUN

DEFINE FILE XYZ
YEAR/YY=DATEFIELD;
MONTH/M=DATEFIELD;
YEARMONTH/YYM=DATEFIELD;
FISYEAR/YY=FIYR(YEARMONTH, 'M', 6, 1, 'FYE', FISYEAR);
END

TABLE FILE XYZ
SUM whatever
BY YEARMONTH NOPRINT
BY MONTH
ACROSS FISYEAR
WHERE YEAR GE &STARTYEAR AND YEAR LE &ENDYEAR;
WHERE MONTH GE &STARTMONTH &OPERATOR MONTH LE &ENDMONTH;
WHERE YEARMONTH GE &STARTYEARMONTH;
END

A more elegant solution:

-SET &STARTYEAR=2013;
-SET &ENDYEAR=2015;
-SET &STARTMONTH=12;
-SET &ENDMONTH=03;
-SET &STARTYEARMONTH = EDIT(EDIT(&STARTYEAR) | EDIT(&STARTMONTH));
-SET &OPERATOR = IF &STARTMONTH GT &ENDMONTH THEN 'OR' ELSE 'AND';
-RUN

DEFINE FILE XYZ
YEAR/YY=DATEFIELD;
MONTH/M=DATEFIELD;
YEARMONTH/YYM=DATEFIELD;
FISYEAR/YY=FIYR(YEARMONTH, 'M', 6, 1, 'FYE', FISYEAR);
END

TABLE FILE XYZ
SUM whatever 
BY FISYEAR
BY YEARMONTH
BY MONTH
WHERE YEAR GE &STARTYEAR AND YEAR LE &ENDYEAR;
WHERE MONTH GE &STARTMONTH &OPERATOR MONTH LE &ENDMONTH;
WHERE YEARMONTH GE &STARTYEARMONTH;
ON TABLE HOLD
END

DEFINE FILE HOLD
MONTHRANK/I1=IF FISYEAR NE LAST FISYEAR THEN 1 ELSE MONTHRANK;
MONTHRANK =IF MONTH NE LAST MONTH THEN MONTHRANK + 1 ELSE MONTHRANK;
END

TABLE FILE HOLD
SUM whatever
BY MONTHRANK NOPRINT
BY MONTH/MONtr
ACROSS FISYEAR
END