I have the following graph code but I am trying to figure out how to use a variable/function that will return current year instead of hardcoding '2009' in the report. And I don't want the user to be prompted.


Please can someone help?

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

-SET &CYEAR=EDIT(&YYMD,'9999');
GRAPH FILE RETAIL
SUM __OVER_30_DAYS_DELNQNT
ACROSS MONTH1
BY ORGANISATION
WHERE ( CONVT_TO_CRRNCY EQ 'EUR' ) AND ( YEAR1 EQ '&CYEAR' );

Instead of 2009 use &DATEYY.

If you need the current month, use &DATEM

If you need the current day, use &DATED.

I find that &DATE followed by any valid date format is quite useful.

For example:

 DO THIS                GET THIS
-TYPE &DATE             10/13/09 
-TYPE &DATEYYMD         2009/10/13              
-TYPE &DATEMtRDYY       October 13, 2009        
-TYPE &DATEMtDYY        Oct 13, 2009            
-TYPE &DATEMTrDYY       OCTOBER 13, 2009        
-TYPE &DATEMTDYY        OCT 13, 2009            
-TYPE &DATEWMDYY        TUE, 10/13/2009         
-TYPE &DATEWRMDYY       TUESDAY, 10/13/2009     
-TYPE &DATEWRMTDYY      TUESDAY, OCT 13 2009    
-TYPE &DATEwrMtDYY      Tuesday, Oct 13 2009    
-TYPE &DATEwrMtrDYY     Tuesday, October 13 2009

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

Hi GamP,
You are too much! It worked. Thank you very much. The next stage of my project is to display the graph for current month and last 3 months.

Please, can you let me know how to get the current month (e.g if December is current month, the value should be '122009'. if July, value= '072009') and how to get the last 3 months.

Thank you.

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

Hi GamP,
You are too much! It worked. Thank you very much. The next stage of my project is to display the graph for current month and last 3 months.

Please, can you let me know how to get the current month (e.g if December is current month, the value should be '122009'. if July, value= '072009') and how to get the last 3 months.

Thank you.[/QUOTE]

I am able to get current month now with
-SET &CMONTH=EDIT(&DMYY,'$$999999');

I am still working on getting last three months. Any idea?

Thanks

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

Look up the AYM function/subroutine. That will allow you to subtract 3 months from an I6YYM format value.

quote:

&DATEYY

I having issues with the following code:

-SET &TODAY_DT = EDIT(&DMYY,'99999999');
DEFINE FILE WHOLESALE
TODAY_DATE/I8DMYY = &TODAY_DT;
CURRENT_DT/YYMD = TODAY_DATE;
INITIAL_DT/M|YY = DATEADD(CURRENT_DT, 'M', -3);
TEST_DT/MtYY = DATEADD(CURRENT_DT, 'M', -3);
END

TABLE FILE WHOLESALE
PRINT
TOTAL_STOCK_IN_CT/I11S AS 'STOCK_IN_CT'
CURRENT_DT
INITIAL_DT
TEST_DT
BY COUNTRY1
BY NEW_USED1
BY FIN_PLAN_MARKETING_DESC1
BY MONTH1
-*ACROSS MONTH1
ON COUNTRY1 SUBTOTAL AS '*TOTAL'
WHERE ( MONTH1 GE '092009' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON

The task is to get report for the last 3 months. The source data is from cube and the month value is in mmyyyy format (e.g for sept, value is 092009). With the WHERE CLAUSE above( MONTH1 GE '092009'), the code returns the correct value. But the plan is not to hardcode the initial month as it will be changing.

The DEFINE fields CURRENT_DT, INITIAL_DT and TEST_DT above have 2009/12/10, 092009 and Sep, 2009 values respectively for sept 2009 (an example). But when I used INITIAL_DT in the WHERE clause (MONTH1 GE 'INITIAL_DT' ), I get the following error:

**********
0 ERROR AT OR NEAR LINE 23 IN PROCEDURE homepage_wh
(FOC006) THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT:
INITIAL_DT
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
**********

Please, can someone help me find out whats wrong and/or solution?

Thank you once again.

I don't have access to run WF anymore but I think you need to remove the | in your date format...
INITIAL_DT/M|YY the slashes should appear automatically due to smart date

You should always do your date tests against literal dates as opposed to defined fields. Here is an example of how to calculate 3 months paste, though depending on what you really want you might want to subtract 4 instead of 3:

-SET &ECHO=ALL;
-SET &CURRMO=&DATEYY | &DATEM;
-SET &MONTH_3=AYM(&CURRMO,-3,'I6YYM');
-SET &TESTMO_3=EDIT(&MONTH_3,'$$$$99') | EDIT(&MONTH_3,'9999');
-TYPE &CURRMO &MONTH_3 &TESTMO_3
...
WHERE ( MONTH1 GE '&TESTMO_3' );
...

quote:

Originally posted by GinnyJakes:
You should always do your date tests against literal dates as opposed to defined fields. Here is an example of how to calculate 3 months paste, though depending on what you really want you might want to subtract 4 instead of 3:

-SET &ECHO=ALL;
-SET &CURRMO=&DATEYY | &DATEM;
-SET &MONTH_3=AYM(&CURRMO,-3,'I6YYM');
-SET &TESTMO_3=EDIT(&MONTH_3,'$$$$99') | EDIT(&MONTH_3,'9999');
-TYPE &CURRMO &MONTH_3 &TESTMO_3
...
WHERE ( MONTH1 GE '&TESTMO_3' );
...

Thank you Ginny. It worked perfectly. I appreciate your assistance.

The only issue is that when I use ACCROSS keyword on MONTH1, the data values does not display in a staight line. Please, do know the reason? the sample code is:

TABLE FILE WHOLESALE
PRINT
TOTAL_STOCK_IN_CT/I11S AS ''
BY COUNTRY1
BY NEW_USED1
BY FIN_PLAN_MARKETING_DESC1
ACROSS MONTH1
ON COUNTRY1 SUBTOTAL AS '*TOTAL'

It displays like the following (in a slanting position):

Sep, 2009............Oct, 2009...............Nov, 2009
39
..............................58
.....................................................26
3491
..............................2610
......................................................2402

I wanted it displayed in a straight line like:
Sep, 2009 Oct, 2009 Nov, 2009
39 58 26
3491 2610 2402

Thank you.

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

As odd as it may sound, change the PRINT to a SUM and all will be well.

It's not that odd ... PRINT means to show one line per individual record - and that's exactly what's happening. With SUM you specify to roll up all detail records to the first aggregate level (BY of ACROSS) and by doing that the across values will also be printed on the same line.

I have tried both SUM and PRINT but the values/measures still do not come in a straight line. One of my colleagues just told me that it's a known webFocus issue and that informationbuilders are working on it.

Is this true? We are using version 769.

Thank you.

No. You've just got some additional sort level that is preventing the row from being aggregated at the level you want. It isn't a WebFOCUS "issue". It's just standard functionality based on how you've coded it.
You'll have to use a SUM with fewer BY fields.