I have the following statement:

DEFINE FILE EFIP_USER_AUDIT_METRICS
LOGIN_YEAR/I4 = HPART(LOGON_DATE,'YEAR','I4');
LOGOFF_MONTH/I2 = HPART(LOGOFF_DATE,'MONTH','I2');
-*
MONTH/A7 = DECODE LOGIN_MONTH(1 'JAN' 2 'FEB' 3 'MAR' 4 'APR' 5 'MAY' 6 'JUN' 7 'JUL' 8 'AUG' 9 'SEP' 10 'OCT' 11 'NOV' 12 'DEC' 13 'JAN 08' 14 'FEB 08' 15 'MAR' 16 'APR' 17 'MAY' 18 'JUN' 19 'JUL' 20 'AUG' 21 'SEP' 22 'OCT' 23 'NOV' 24 'DEC');
-*
END


DEFINE FILE EFIP_USER_AUDIT_METRICS
PRINT
LOGIN_MONTH
LOGIN_YEAR
LCOUNTER
MONTH

BY OS_USERNAME
BY LOGON_DATE
BY USERNAME
WHERE MASTER_SESSION EQ 1
-*
&strSCHEMA.EVAL <- from selection Screen
&strMONTH.EVAL
&strYEAR.EVAL
ON TABLE HOLD AS USR_DATA
END

ON GRAPH SET LOOKGRAPH LINE
-*
-GOTO STYLEFLD;
-*
-SKIPBY
-*
SUM
MAX.LCOUNTER AS 'Concurrent Users'
BY USERNAME AS ' '

-*ACROSS MONTH COLUMNS JAN AND FEB AND MAR AND APR AND MAY AND JUN AND JUL AND AUG AND SEP AND OCT AND NOV AND DEC AND JAN AND FEB AND MAR AND APR AND MAY AND JUN AND JUL AND AUG AND SEP AND OCT AND NOV AND DEC
ACROSS LOGIN_YEAR NOPRINT
-*
ON GRAPH SET LOOKGRAPH LINE
END

The issue I'm having is that is the months between 2007 and 2008 are coming in as 1 thru 12 only. So, the display will come out as Jan Feb Feb Mar Mar....if there are no values for the months, it will not display. So really the issue I am having is that 2008 is being read as 1 thru 12 rather then 13 thru 24. How can I fix this?

Thanks in advance.

This message has been edited. Last edited by: Kerry,

Since you have 'by year', you apparently want to report the 12 months of 2008, and below that the months of 2009.

Define the year_and_month, and its year and month components, as 'smart dates'. Then you can use numeric values 1 to 12 in the COLUMNS phrase to force all 12 months to display by name, as below.

-* get specimen data from 1/2008 thru 12/2009,
-* with some gaps.
DEFINE FILE GGSALES
START/MYY='DEC 2007';
YYM/YYM=START+SEQ;
END
TABLE FILE GGSALES
SUM UNITS
BY SEQ
BY YYM
IF SEQ FROM 1 TO 24
IF SEQ NE 9 OR 14 OR 21
ON TABLE HOLD
END
-RUN

-* report by year across month, forcing all 12 months to appear 
DEFINE FILE HOLD
 YEAR/YY=YYM;
 MONTH/MT=YYM;
END
TABLE FILE HOLD
 SUM UNITS
 BY YEAR
 ACROSS MONTH COLUMNS 1 AND 2 AND 3 AND 4 AND 5 AND 6 AND 7 AND 8 AND 9 AND 10 AND 11 AND 12
END

Just to note, I meant to have login_year in an across and not a by, I apologize for that. Just to be clear, I want to display 24 months on a graph.

OK, but now what's your problem?

getting a single "across" variable for year & month?
Handling missing months?

I was able to use your structure and logic to situate myself. what I did was,

-first In the define I created the following:

LOGIN_YRVAL/I2=IF LOGIN_YEAR EQ 2008 THEN 12 ELSE 0;
LOGIN_MONVAL/I2=LOGIN_MONTH + LOGIN_YRVAL;
MONTH/A9 = DECODE LOGIN_MONVAL(1 'JAN 07' 2 'FEB 07' 3 'MAR 07' 4 'APR 07' 5 'MAY 07' 6 'JUN 07' 7 'JUL 07' 8 'AUG 07' 9 'SEP 07' 10 'OCT 07' 11 'NOV 07' 12 'DEC 07' 13 'JAN 08' 14 'FEB 08' 15 'MAR 08' 16 'APR 08' 17 'MAY 08' 18 'JUN 08' 19 'JUL 08' 20 'AUG 08' 21 'SEP 08' 22 'OCT 08' 23 'NOV 08' 24 'DEC 08');

THEN IN THE GRAPH I used the following:

SUM DST_OS_USERNAME AS 'Unique Users'
BY USERNAME AS 'User Names'
ACROSS MONTHS COLUMNS 'JAN 07' AND 'FEB 07' AND 'MAR 07' AND 'APR 07' AND 'MAY 07' AND 'JUN 07' AND 'JUL 07' AND 'AUG 07' AND 'SEP 07' AND 'OCT 07' AND 'NOV 07' AND 'DEC 07' AND 'JAN 08' AND 'FEB 08' AND 'MAR 08' AND 'APR 08' AND 'MAY 08' AND 'JUN 08' AND 'JUL 08' AND 'AUG 08' AND 'SEP 08' AND 'OCT 08' AND 'NOV 08' AND 'DEC 08'
-*ACROSS LOGIN_YEAR NOPRINT
ON GRAPH SET LOOKGRAPH LINE
END

Appreciate your help J.Gross, Thanks!

quote:

Originally posted by j.gross:
Since you have 'by year', you apparently want to report the 12 months of 2008, and below that the months of 2009.

Define the year_and_month, and its year and month components, as 'smart dates'. Then you can use numeric values 1 to 12 in the COLUMNS phrase to force all 12 months to display by name, as below.

-* get specimen data from 1/2008 thru 12/2009,
-* with some gaps.
DEFINE FILE GGSALES
START/MYY='DEC 2007';
YYM/YYM=START+SEQ;
END
TABLE FILE GGSALES
SUM UNITS
BY SEQ
BY YYM
IF SEQ FROM 1 TO 24
IF SEQ NE 9 OR 14 OR 21
ON TABLE HOLD
END
-RUN

-* report by year across month, forcing all 12 months to appear 
DEFINE FILE HOLD
 YEAR/YY=YYM;
 MONTH/MT=YYM;
END
TABLE FILE HOLD
 SUM UNITS
 BY YEAR
 ACROSS MONTH COLUMNS 1 AND 2 AND 3 AND 4 AND 5 AND 6 AND 7 AND 8 AND 9 AND 10 AND 11 AND 12
END

OMG this is wonderful! A one-line fix to something I've been fighting all morning. I must admit I'm not quite sure how exactly it works, but I'll take it! Thanks!