Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] DECODE to dynamically control order in which 12 months...

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] DECODE to dynamically control order in which 12 months...
 Login/Join
 
Master
posted
Hello,

I am trying to create a DECODE to dynamically control the order in which 12 months of a
year to display in a report. The date will be provided via USER input.

THe month will be extracted from the user input.

For example, the user entered 10/15/2019 as October will show up first in the results.
The DECODE will be like:

MORDER/12 = DECODE INONTH(10 '1' 11 '2' 12 '3' 
                           1 '4' 2  '5'  3 '6'  
                           4 '7' 5  '6'  7 '8' 
                           8 '9' 10 '8' 11 '9');


For another example, the user entered 06/10/2018 as June will show up first in the results.
The DECODE will be like:

MORDER/12 = DECODE INONTH(6  '1'  7  '2'  8 '3'
			  9  '4' 10  '5' 11 '6'
 			 12  '7'  1  '8'  2 '9'
                          3 '10'  4 '11'  5 '12');


I would appreciate any ideas anyone can provide.

Programming is a wonderful release, but some days, unlike Hedley Lamarr in "Blazing Saddles",
I do not have "the rivulets of thought, cascading into a waterfall of creative alternatives."

Thank you.

This message has been edited. Last edited by: FP Mod Chuck,


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
What's the format of the date field?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
yymd


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Virtuoso
posted Hide Post
Not have been tested, but logically seems to work
Where MthFld is a TABLE field
Where UsrSelectedMth it's the extracted month from user selection
DEFINE FILE xyz
subMth /I2 = IF UsrSelectedMth EQ 1 THEN 0 ELSE UsrSelectedMth - 1;
addMth /I2 = 12 - subMth;
mthOrd /I2 = IF MthFld GE UsrSelectedMth THEN MthFld - subMth ELSE MthFld + addMth;
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Is MthFld defined here?

mthOrd/I2 = IF MthFld GE usrSelectdMth THEN MthFld - subMth ELSE MthFld + addMth ; 


DEFINE FILE WF_RETAIL
THEDATE/YYMD WITH ID_SALES = '&YYMD'; 
UsrSelectedMth/I2 = DPART(THEDATE, 'MM', 'I11');
subMth/I2 = IF UsrSelectedMth EQ 1 THEN 0 ELSE UsrSelectedMth - 1 ;
addMth/I2 = 12 - subMth ;
mthOrd/I2 = IF MthFld GE usrSelectdMth THEN MthFld - subMth ELSE MthFld + addMth ; 
END

TABLE FILE WF_RETAIL
PRINT
UsrSelectedMth
subMth
addMth
-*mthOrd
BY THEDATE
IF RECORDLIMIT EQ 1
END 

-EXIT


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
One possible model:
DEFINE FILE GGSALES
 INDATE/YYMD  = '20090601';
 INMONTH/M    = INDATE;
 ININT/I2     = INMONTH;
-* 
 FILEMONTH/M  = DATE;
 FILEINT/I2   = FILEMONTH;
-* 
 SORTORDER/I2 = IF FILEINT EQ ININT THEN 1 
                 ELSE IF FILEINT GT ININT THEN FILEINT - ININT + 1
		          ELSE (12 - ININT + 1) + FILEINT;
END
TABLE FILE GGSALES
PRINT REGION  NOPRINT 
      INDATE  AS 'Incoming Date'
      INMONTH NOPRINT  
      ININT   NOPRINT    
-*
      DATE      AS 'File Date'
      FILEMONTH NOPRINT  
      FILEINT   NOPRINT  
-*	  
      SORTORDER AS 'Sort Order'
BY    SORTORDER NOPRINT
IF RECORDLIMIT EQ 30
END    

Incoming Date   File Date  Sort Order                                         
  -------------   ---------  ----------                                         
  2009/06/01     1997/06/01           1
  2009/06/01     1997/06/01           1
  2009/06/01     1997/07/01           2
  2009/06/01     1997/07/01           2
  2009/06/01     1996/07/01           2
  2009/06/01     1997/07/01           2
  2009/06/01     1997/08/01           3
  2009/06/01     1996/09/01           4
  2009/06/01     1997/09/01           4
  2009/06/01     1996/09/01           4
  2009/06/01     1997/09/01           4
  2009/06/01     1996/09/01           4
  2009/06/01     1997/09/01           4
  2009/06/01     1997/10/01           5
  2009/06/01     1997/10/01           5
  2009/06/01     1996/10/01           5
  2009/06/01     1996/10/01           5
  2009/06/01     1996/11/01           6
  2009/06/01     1996/11/01           6
  2009/06/01     1997/11/01           6
  2009/06/01     1996/12/01           7
  2009/06/01     1997/12/01           7
  2009/06/01     1996/12/01           7
  2009/06/01     1997/01/01           8
  2009/06/01     1997/03/01          10
  2009/06/01     1997/03/01          10
  2009/06/01     1997/04/01          11
  2009/06/01     1997/04/01          11
  2009/06/01     1997/05/01          12
  2009/06/01     1997/05/01          12
  
 
Posts: 822 | Registered: April 23, 2003Report This Post
Virtuoso
posted Hide Post
quote:
Originally posted by Tomsweb:
Is MthFld defined here?

As I mentioned : "Where MthFld is a TABLE field"
A field that is coming from you DB/Table; sure that may need to be a DEFINED one since normally we are keeping a date field and not its components individually. So yes you may need to extract the month component of your date field.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
thanks everyone for your suggestions


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
I would go with Davids suggestion but would simplify(?) the defines -

DEFINE FILE GGSALES
 INDATE/I8YYMD  = 19960301;
 INMONTH/I2     = DTPART(DATECVT(INDATE, 'I8YYMD', 'YYMD'), MONTH);
-* 
 FILEMONTH/I2   = DTPART(DATECVT(DATE, 'I8YYMD', 'YYMD'), MONTH);
-* 
 SORTORDER/I2 = IF (13 - INMONTH + FILEMONTH) GT 12 THEN (13 - INMONTH + FILEMONTH - 12) 
                 ELSE (13 - INMONTH + FILEMONTH);
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
For David Briars's SORTORDER, perhaps even simpler:

SORTORDER/I2 = MOD(FILEINT - ININT + 12, 12) + 1 ;


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] DECODE to dynamically control order in which 12 months...

Copyright © 1996-2020 Information Builders