Hi All,

I have a graph. Which have 2 filter. One is year and one is months(multiselect).
The Query is-
If i select year 2015 and months (Jan,Feb,March),Graph will populate 3 bar for each Employee group.
Every bar have average value from selected month to last 12 months back data.For Example-
Bar 1 which is Jan - Average data of(Feb 2014-Jan 2015.)
Bar 2 which is Feb - Average data of(March 2014-Feb 2015.)
Bar 3 which is March - Average data of(Apr 2014-March 2015.)



Please Provide the Needful solution ASAP.

Thanks in advanced.

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

Do not start with the chart straight away.
First, for each employee and month, get the average for prior 12 months.

Just an example of how i would handle it,

Have two hold files.

1st containing Employee and YearMonth columns.
2nd hold file containing Employee, YearMonth and metric column.

Then join or merge two files on Employee and built the logic to get average for prior 12 months.

There are also other ways like Mcguyver technique etc..

-DEFAULT &MYYEAR = 2015 ;
-DEFAULT &MYMONTH = 3 ;
-SET &ENDYYM = &MYYEAR * 100 + &MYMONTH ;
-SET &startyym = AYM(&ENDYYM , -13 , 'I6YYM');

-SET &bar1_start = &startyym ;
-SET &bar1_end = AYM(&startyym , 11 , 'I6YYM');
-SET &bar2_start = AYM(&startyym, 1 , 'I6YYM');
-SET &bar2_start = AYM(&bar2_start, 11 , 'I6YYM');
-SET &bar3_start = AYM(&startyym, 2 , 'I6YYM');
-SET &bar3_start = AYM(&bar3_start, 11 , 'I6YYM');
-? &b
-* that command will show you the &vars you've calculated so far
-*
DEFINE FILE THING
DATEYYM/A6 = YEAR | MONTH ;
END

TABLE FILE THING
SUM VALUE
AND COMPUTE bar1/I8 MISSING ON =IF DATEYYM FROM &bar1_start TO &bar1_end THEN VALUE ELSE MISSING ;
AND COMPUTE bar2/I8 MISSING ON =IF DATEYYM FROM &bar2_start TO &bar2_end THEN VALUE ELSE MISSING ;
AND COMPUTE bar3/I8 MISSING ON =IF DATEYYM FROM &bar3_start TO &bar3_end THEN VALUE ELSE MISSING ;
BY EMGROUP
BY DATEYYM
IF DATEYYM FROM &STARTYYM TO &ENDYYM
ON TABLE HOLD
END
-RUN
-* now take a peek at your data to make sure you like it, then proceed
GRAPH FILE HOLD
SUM AVE.bar1 AVE.bar2 AVE.bar3
BY EMGROUP
...whatever else you need
END