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

[#1]

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!

[#2]

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,

M

[#3]

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?

[#4]

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

[#5]

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

[#6]

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. 

[#7]

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....

[#8]

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.