Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
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...
Go
New
Search
Notify
Tools
Reply
  
[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: 553 | Location: Baltimore, MD | Registered: July 06, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
What's the format of the date field?


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport 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: 553 | Location: Baltimore, MD | Registered: July 06, 2006Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport 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: 553 | Location: Baltimore, MD | Registered: July 06, 2006Reply With QuoteReport 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: 780 | Registered: April 23, 2003Reply With QuoteReport 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.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2223 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport 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: 553 | Location: Baltimore, MD | Registered: July 06, 2006Reply With QuoteReport 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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5616 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
For David Briars's SORTORDER, perhaps even simpler:

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


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 141 | Registered: October 19, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.