Hi there,

How can I represent Fiscal Year instead of Jan to Dec in my graph for several years. I'm working on a line graph and I want 4 lines representing fiscal year Net Sales for 2004,2005,2006,2007.

Can anybody help me please...

Trina

It depends of course how this fiscal year is defined (I do not know if this is something static in the US financial world). But if p.e. the fiscal years goes from 1-apr till 31-mar next year you can define a new sequence period.

DEFINE FILE XXX
NYEAR/YY=DATE; (gives the normal year)
NMONTH/M=DATE; (gives the month number) 
FYEAR/I4=IF NMONTH LT 4 THEN NYEAR - 1 ELSE NYEAR;
END

Thanks Frank, by Fiscal Year 2005 I meant Nov(2004) to Oct(2005). I've used a counter
and it works fine but the problem is it shows number labels and I want month spelled out like
Nov,Dec,Jan,Feb ....

Here is the code I used in case you need to take a look.

GRAPH FILE ALLDATA
PRINT
NETSALES AS ''
BY FYEAR
ACROSS CTR AS ''

Now insted of "ACROSS CTR" in the code (last line) if I use "ACROSS MTH", it shows
Jan,Feb,Mar....... but I want Nov,Dec,Jan....as label.

This question made me search in Google.
I found that the USA official fiscal year is from the 1-October till 30-September. If you have to deal with a lot of different countries in this, you can create a separate country table with these settings

Try this code,

To test this, create MONTH.txt file and that has to have the values like
JAN
FEB
..
..
DEC

Store this file under your apps directory and call that file in your fex.

-*SET &ECHO='ALL';

FILEDEF MONTHDAT DISK \\&servname\IBI\APPS\MONTH.TXT
-RUN

-**input
-SET &MON1='JUN';
-*changing the input corresponding to month.
-SET &SELMONTH=DECODE &MON1('JAN' '0' 'FEB' '1' 'MAR' '2' 'APR' '3' 'MAY' '4'
- 'JUN' '5' 'JUL' '6' 'AUG' '7' 'SEP' '8' 'OCT' '9' 'NOV' '10' 'DEC' '11');

-SET &STARTMON = 12 - EDIT(&SELMONTH);
-SET &CNT=0;
-SET &MONVAL='';

-CONTRD
-READ MONTHDAT &MON.A3
-IF &IORETURN NE 0 THEN GOTO ENDRD ELSE GOTO GETMON;
-GETMON
-IF &MON EQ &MON1 OR &CNT GT 0 THEN GOTO STARTONE ELSE GOTO STARTTWO;
-STARTONE
-SET &MONVAL&CNT.EVAL=&MON ;
-SET &CNT=&CNT+1;
-GOTO CONTRD;

-STARTTWO
-SET &MONVAL&STARTMON.EVAL=&MON;
-SET &STARTMON=&STARTMON+1;
-GOTO CONTRD;
-ENDRD

-SET &CNT=0;
-REPEAT MONVAL 12 TIMES
-TYPE &MONVAL&CNT.EVAL
-SET &CNT=&CNT+1;
-MONVAL
-EXIT

Run the above code and you will get some idea.

If you want to put this code in to your program. All you have to do is,
-SET &CNT=0;
-REPEAT MONVAL 12 TIMES
-*TYPE &MONVAL&CNT.EVAL
setSeriesLabel(&CNT,&MONVAL&CNT.EVAL)
-SET &CNT=&CNT+1;
-MONVAL
ENDSTYLE
END


Hope it helps,

Trina

I did not realize you wanted this on a month by month basis.
In a table it would be easy by using two across fields, but in a graph you can only use one across.
Interesting problem in fact....
I'm afraid a label like "01-nov" "02-dec" "03-jan" etc will be unacceptable I think?

Trina

I was thinking on this interesting problem and thought this might be a solution (not tested...)

-SET &FSTART1 = '20031001';
-SET &FSTART2 = '20041001';
-SET &FSTART3 = '20051001';
-SET &FSTART4 = '20061001';
-SET &FSTART5 = '20071001';
DEFINE FILE SALES
PLOTDATE/YYMD=IF REALDATE GE '&FSTART1' AND REALDATE LT '&FSTART2' THEN REALDATE ELSE
              IF REALDATE GE '&FSTART2' AND REALDATE LT '&FSTART3' THEN DATEADD(REALDATE,'Y',-1) ELSE
              IF REALDATE GE '&FSTART3' AND REALDATE LT '&FSTART4' THEN DATEADD(REALDATE,'Y',-2) ELSE
              IF REALDATE GE '&FSTART4' AND REALDATE LT '&FSTART5' THEN DATEADD(REALDATE,'Y',-3) ;
SALEM3/D12=   IF REALDATE GE '&FSTART1' AND REALDATE LT '&FSTART2' THEN SALES ELSE 0;
SALEM2/D12=   IF REALDATE GE '&FSTART2' AND REALDATE LT '&FSTART3' THEN SALES ELSE 0;
SALEM1/D12=   IF REALDATE GE '&FSTART3' AND REALDATE LT '&FSTART4' THEN SALES ELSE 0;
SALEM0/D12=   IF REALDATE GE '&FSTART4' AND REALDATE LT '&FSTART5' THEN SALES ELSE 0;
END

TABLE FILE SALES
SUM
SALEM0 SALEM1 SALEM2 SALEM3
BY REALDATE
BY PLOTDATE
ON TABLE HOLD
END
-* will give
20031001 20021001    0     0     0   100
20031101 20021101    0     0     0   200
20031201 20021201    0     0     0   150
...
20051001 20021001    0   300     0     0
20060201 20030201    0   255     0     0
...


GRAPH FILE HOLD
SUM SALEM0 SALEM1 SALEM2 SALEM3
ACROSS PLOTDATE
....
...

END

The result in the graph will give you labels you will not like, but there is an option to change it.
(I found this in wf713graph.pdf)


Time Scale Axis Graphs

A time scale axis uses time to display data. Clustered Bar, Stacked Bar, Line, and Scatter
graphs may use a time scale axis. The following time intervals can be specified in each of
these graph types:
TIME_INTERVAL_UNDEFINED = 0
TIME_INTERVAL_SECONDS = 1
TIME_INTERVAL_MINUTES = 2
TIME_INTERVAL_HOURS = 3
TIME_INTERVAL_DAYS = 4
TIME_INTERVAL_WEEKS = 5
TIME_INTERVAL_MONTHS = 6
TIME_INTERVAL_QUARTERS = 7
TIME_INTERVAL_YEARS = 8
It is often useful to represent data with several intervals. Three intervals can be shown
simultaneously on a time axis scale. The following example shows weeks, months, and
years.
The level intervals must be set in decreasing order -- the level zero interval must be less
than level one, level one must be less than level two. In this example, the following
methods were used to set the level intervals:

setLevelInterval(1, Interval.TIME_INTERVAL_WEEKS);
setLevelInterval(1, Interval.TIME_INTERVAL_MONTHS);
setLevelInterval(1, Interval.TIME_INTERVAL_YEARS);


I think you should only use months.

The overall advantage for this solution is that you can also use it for the normal calendar year.

Thanks kamesh! I tried to run your code to see how it works but unfortunately I got the following error......

(FOC295) A VALUE IS MISSING FOR: MONVAL5

Thanks Frank! I’ll try according to your suggestion. By the way, where can I find wf713graph.pdf?

Trina

Trina

you need to be a registered user on IBI

look at http://documentation.informationbuilders.com/masterindex/

This dos is in the top list downloaded docs.

If you need any help please ask.
I will give this a try myself tomorrow.

This is the code you need to add...

setUseTimeScaleAxis(true);
setLevelInterval(0,6);
setLevelTextFormat(0,1);

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

Hi Frank,

I've got my graph labeled as:
2004/11/01 2004/12/01 2005/01/01 2005/02/01 2005/03/01.....2005/10/01 for several years. So, I’m having 5 different lines for 5 different years based on the above labels.

Now I'm stuck trying to represent this dates to month (Nov-Oct) according to your suggestion. I've added the following lines in my code

setUseTimeScaleAxis(true);
setLevelInterval(0,6);
setLevelTextFormat(0,1);

Having the following error:

Possible Error on:setLevelTextFormat(0.0,1.0);

Please let me know if you've any success.

Thanks!

Trina

can you send the data, the master and the fex you build so far.
put it in a zip file and sent it to my gmail account. I can take a look tomorrow.
I tried it on some fake data and it worked.

Sure I can do that. Let me know your gmail account. Frank, can you please send me the graph code that you did on some fake data?

By the way Frank, I saw your summit pictures, they all came out very nice!

Trina

my mail is

frank.terlien@gmail.com

did you go to the summit?

No, I've plan for next year though.

Trina
you might be able to get away with the COLUMNS command in your graph fex.
I use it for a line graph for apparel sizes XS S M L XL, which has no reasonable meaning when in alpha order.
For me,
ACROSS SIZENAME
COLUMNS XS AND S AND M AND L AND XL
works really well in a line graph.
You might be able to use the same concept to put your months in order, if you have defined a field containing just the alpha version of the monthname:
Take AmsterdamFrank's define code
NMONTH/M=DATE; (gives the month number)
add this line
MONTH/A3=DECODE NMONTH( 1 'JAN' 2 'FEB' ... etc)
then...
ACROSS MONTH
COLUMNS NOV AND DEC AND JAN ...etc
The columns command overrides the alpha ordering of the values in the MONTH field.
(I got this idea ages ago here on the Point from, i think, Pietro.)
Any help?

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

Thanks a lot Susannah! It works perfect! Small little code but does a big thing.

Thanks Frank and Kamesh as well.

Trina

I know this thread is a few weeks old, but I just stumbled onto it while looking for something else and it actually answered a question I've had on my mind for awhile now.

Thanks susannah for a great technique!