Focal Point
[CLOSED] Can you sort across by date?

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3997051196

March 06, 2019, 11:57 AM
intsoccersuperstar
[CLOSED] Can you sort across by date?
I'm using the following line:

ACROSS STATUS_CHANGE_DATE_MODIFIED AS 'Month'

Giving me the below output:

Month
01-2019 02-2019 03-2019 12-2018

I'd like 12-2018 to be left most and then continue chronologically.

My STATUS_CHANGE_DATE_MODIFIED is determined using SQL like so:

to_char(stat.status_change_date, 'MM-YYYY') status_change_date_modified,

Any help would be greatly appreciated! Thanks!

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


intsoccersuperstar
WebFOCUS 8105m
Windows, Excel
March 06, 2019, 12:54 PM
MartinY
It seems that STATUS_CHANGE_DATE_MODIFIED is not a date but a char due to : to_char(stat.status_change_date, 'MM-YYYY'), reason why 01-2019 is ordered before 12-2018
If it was a date 12-2018 was placed before 01-2019

You should better keep the date as a date and use IB format to have the date displayed as MM-YYYY

something such as
DEFINE FILE GGSALES
DATEMMYYYY /MYY = DATE;
END
TABLE FILE GGSALES
SUM DOLLARS
BY DATEMMYYYY
END

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


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
March 06, 2019, 01:10 PM
Tom Flynn
Add another SQL column and use as your sort:
to_char(stat.status_change_date, 'YYYY-MM') status_change_date_sort

In your ACROSS:
ACROSS STATUS_CHANGE_DATE_SORT NOPRINT
ACROSS STATUS_CHANGE_DATE_MODIFIED AS 'Month'


Tom Flynn
WebFOCUS 7.7.05 - PROD/QA
DB2 - AS400 - Mainframe