Focal Point
[CLOSED] Date on the Horizontal Axis of Bar Chart

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

April 08, 2020, 05:53 PM
AMC2
[CLOSED] Date on the Horizontal Axis of Bar Chart
I have a date field (defined as an integer) as the value on the X axis of a vertical bar chart. In the GUI of InfoAssist, when I change the format of that date field on the X axis to display it in like 99/99/9999, it removes the date field completely from the X axis.

Has anyone seen this or have any suggestions on how to fix?

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


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
April 08, 2020, 06:21 PM
BabakNYC
Could you give us the FORMAT= value of this date field from the master file?


WebFOCUS 8206, Unix, Windows
April 08, 2020, 07:22 PM
AMC2
It is I11.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
April 09, 2020, 09:21 AM
Doug
quote:
defined as an integer
Why is it "defined as an integer? Integers cannot contain Alpha Characters, such as a "/" (slash).
April 09, 2020, 10:41 AM
BabakNYC
What's the data source? Usually a dbms maintains a real date field. If this is a CSV or Excel, you might want to change the format at the load time to make it represent a real date.


WebFOCUS 8206, Unix, Windows
April 09, 2020, 11:30 AM
AMC2
Doug - I was trying to redefine the integer as date within InfoAssist.

BabakNYC - SQL Server is the data source.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
April 09, 2020, 11:52 AM
BabakNYC
Not clear to me why you'd do that. Without a very specific use case or more details about what you're trying to do, the answers you'll get will be vague. It's much easier to give advice when you understand the context of the question. Showing code or examples are also helpful.

Here's an example of converting a DATE field to an Integer for instance to get a component of the date.

LASTFMDATE_MONTH/I2= DTPART(LASTFMDATE, MONTH);


WebFOCUS 8206, Unix, Windows
April 09, 2020, 12:36 PM
AMC2
Sorry if I'm not clear here. The field is defined as an Integer in the SQL Server data source and it is defined as I11 in the master file. When I use it on the X axis of the bar chart in InfoAssist, I get 20200101 for example. I am trying to display that as 01/01/2020 on the X axis in InfoAssist.

When I go to format the date to display as 01/01/2020 in InfoAssist by selecting MM/DD/YYYY, it removes the date field completely off the horizontal axis. Does this detail make it a little clearer about what I'm trying to do and the behavior in InfoAssist?


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
April 09, 2020, 05:38 PM
AMC2
Babak - Does that make it a little clearer?


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
April 09, 2020, 05:59 PM
Hallway
You cannot just change the format of an integer field to a date field. You will need to set a DEFINE and convert the integer to a date.

Example:
  
DEFINE FILE car
SAMPLE_INT_DATE/I11 = '20200101';
NEW_DATE/MDYY = DATECVT(SAMPLE_INT_DATE, 'I8YYMD', 'MDYY');
END

TABLE FILE car
PRINT
    SAMPLE_INT_DATE
    NEW_DATE
BY COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1
END



Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
April 09, 2020, 07:13 PM
BabakNYC
Yes that does make it clear. In my considered opinion, you should talk to the DBA or the owner of this table, and convince them to change this column to a real date. Even though you can manipulate an Integer that has representation of a date, creating real date calculations such as Age, Yesterday, Tomorrow, Previous Year, YTD et cetera becomes a real maintenance nightmare. If this column is a date, I vote you make it a date in the DBMS so you don’t have to compensate for this in DEFINEs and COMPUTES. The alternative to that is to use Hallway’s technique, dump the data into a HOLD file and then you’ll have real date fields to create charts and reports for.


WebFOCUS 8206, Unix, Windows
April 09, 2020, 07:49 PM
Hallway
quote:
Originally posted by BabakNYC:
Yes that does make it clear. In my considered opinion, you should talk to the DBA or the owner of this table, and convince them to change this column to a real date. Even though you can manipulate an Integer that has representation of a date, creating real date calculations such as Age, Yesterday, Tomorrow, Previous Year, YTD et cetera becomes a real maintenance nightmare. If this column is a date, I vote you make it a date in the DBMS so you don’t have to compensate for this in DEFINEs and COMPUTES. The alternative to that is to use Hallway’s technique, dump the data into a HOLD file and then you’ll have real date fields to create charts and reports for.


Or just create that define in the master file so you don't have to completely mess up the architecture of the database and any other application that might use that field differently.


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
April 13, 2020, 12:28 PM
AMC2
Thanks to both of you. I may have to create a DEFINE at the procedure or master file level.


WebFOCUS 8.2.06
SQL Server
HTML, PDF, Excel, etc
April 18, 2020, 12:38 PM
Doug
What Hallway said, using DATACVT, should work.