topic Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year) in Discussions

Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

saitcopuroglu — Fri, 12 Feb 2016 12:10:58 GMT

Hi,

I need to convert the Date column in format of DMY to DM so I can make analysis of Years as Overlapping. Tried "Date DMY" function and deleting the "year" part but did not help. Need it for two different analysis. The first is the overlapping Sales Data. The attached table "DateTimeFunctionDM" consist of dates which has sales both for two different years (overlapping). Each day producing a sales report comparing Year 2016 and 2015 till the date of report I first need to extract the Day and Month form the DMY format and use the Year as "Overlay". Second job would be creating a column with such a filter that regardless from the Year it filters the sales till DateMonth of Years (lets say if I create the report today it filters the Both the Years till 12/02 to compare both years sales till that date)

At another analysis I need to do the same to compare the price fluctuations of Periods between Years. Thus I need to create again a column which displays numeric date column from 05May2014 as 05May. (Table "Periods" attached)

Man thanks in advance for your support!

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

MikeD_Anderson — Fri, 12 Feb 2016 13:18:15 GMT

You could try something like this (using the first column from that periods.mp dataset):

Left(Format Date(:PeriodBegin, "ddMonyyyy"), 5)

The downside to doing it this way is that it is no longer numeric.

Best,

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

saitcopuroglu — Fri, 26 Feb 2016 20:58:17 GMT

Thank you michael.anderson it could help the Period problem by characterising and combining them into one column but filtering and working with numeric data need some other solution.

Any ideas?

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

Eric_Hill — Wed, 19 Oct 2016 02:04:04 GMT

Hey, saitcopuroglu,

I came up with two alternatives, not sure if either really solves your problem. The first, which you may have tried, was a formula column like this:

DateDMY( Day(:PeriodBegin), Month(:PeriodBegin), 1904 );

So just fix the year at 1904, which is really year 0 for JMP date-time values. JMP does *not* offer a format with just month and day, and JMP does not have a custom formatter a la Excel that would allow you to put in letters to get just what you want. So with that approach, you are going to see the 1904

Another approach I took was:

Month(:PeriodBegin) * 100 + Day(:PeriodBegin)

That gives you something that is a number but only has the month and day in it.

But now it is not a real date, so you can't use date functions on it to extract the month or day - you'd have to do your own math.

Anyway, there are some other ideas in case they might help.

Thanks,

Eric

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

vince_faller — Wed, 19 Oct 2016 02:05:24 GMT

You could do DayofYear() or WeekofYear() plus a Year() column. It won't give you an exact date but it will be comparable (Day of year may be off with leap years).

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

vince_faller — Wed, 19 Oct 2016 02:05:34 GMT

Or even just break it out with a column for each day, month and year.

I might be missing what you're trying to do though.

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

saitcopuroglu — Sat, 05 Mar 2016 08:20:33 GMT

Thank you for the inputs.

Both approach the task in different ways but still lacking the JSL automation abilities.

The script should consist of "Sales Date ≦Today()" filtering for a real automation (otherwise a manual Local Data Filter selection is still a must for an everyday report)

If the created columns suggested by vince.faller0 or EricHill or michael.anderson what is the way of writing the JSL which filters the columns as with the logic of "Sales Date ≦Today()" ?

Many thanks in advance

P.S.: Maybe the Developers could add a function under "Date and Time" which converts the ddmmyyyy format to ddmm and still can be approached and used as Date Data and Today() function may dismiss the Year?.... Just an idea....

Re: Date Functions Converting DMY to DM (filtering everyday by Today() function without year)

vince_faller — Mon, 07 Mar 2016 13:14:56 GMT

Dt << Select Where(Month(:Sales Date)> Month(Today()) & Day(:SalesDate )>Day(Today())) <<Hide << Exclude

I don't see why that wouldn't work, unless I'm misunderstanding.