[CLOSED] Date on the Horizontal Axis of Bar Chart

April 08, 2020, 05:53 PM

AMC2

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,

April 08, 2020, 06:21 PM

BabakNYC

Could you give us the FORMAT= value of this date field from the master file?

April 08, 2020, 07:22 PM

AMC2

It is I11.

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.

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.

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);

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?

April 09, 2020, 05:38 PM

AMC2

Babak - Does that make it a little clearer?

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

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.

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.