Distinct date from timestamp

[viveksingh]

Hi Experts,

I have CALENDAR_DATE field in Time stamp format, which is like 2001-01-13 00:00:00.000000

I  extracted Date from the Time stamp with  Date(Timestamp(CALENDAR_DATE),'MMM-YY')

and the output shows as

JAN-01

JAN-01

FEB-01

JAN-02

FEB-02

FEB-02

JAN-03

FEB-03

I want this as which is unique records along with sort.

JAN-01

FEB-01

JAN-02

FEB-02

JAN-03

FEB-03

Thanks,

Vivek

[arulsettu]

can you post sample data

[viveksingh]

Hi Arul,

my date field is in TIMESTAMP format like 2001-01-13 00:00:00.000000

when I use with below expression

I will get output like

I want this to be in UNIQUE and it needs to be sort.

[arulsettu]

if your timestamp is like this 2001-01-13 00:00:00.000000

then your output should be

2001-01-13

that means

jan-01

right?

post some excel data

[rahulpawarb]

Hello Vivek,

You can use below given sample code snippet in script.

Date(Date#(Left(CALENDAR_DATE,10), 'DD/MM/YYYY'), 'MMM-YY') AS Date

Hope this will be of help!

Regards!

Rahul

[viveksingh]

Yes, you are correct. I Just wrote as an example.

Below are my sample data

[vinieme12]

use DATE#( Date(Timestamp(CALENDAR_DATE),'MMM-YY'))

[viveksingh]

Hi Vineeth,

Thanks for the reply, expression is working fine . but we need to sort it in a chart.

now it it showing as

APR-01,APR-03,AUG-01,FEB-01.

it should sort as per calendar like FEB-01,APR-01,AUG-01,APR-03

[rahulpawarb]

Hello Vivek,

You can sort the dimension based on expression (Chart Properties-> Sort Tab). If the dimension field is Date then use

Date(Date,'YYYYMM') in expression box and set the sort order as Ascending.

Regards!

Rahul

[PrashantSangle]

Hi,

use floor and date() in script.

try below

date(floor(dateField),'MMM-YY') as monthYear

and use new field in your object.

Regards,