Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik__Vinodh
Partner - Creator II
Partner - Creator II

Year & Date problem

Hi friends,

i have a problem in date field.

1. i am not able to get year in attached test application.

if any one have soluation please let know.

Regards,

vindh

9 Replies
Qlik__Vinodh
Partner - Creator II
Partner - Creator II
Author

Hi all,

any one help me on this

er_mohit
Master II
Master II

could not open your file as far i use personal edition

could you plese share your script or what you exactly want in output

Qlik__Vinodh
Partner - Creator II
Partner - Creator II
Author

year field is required from date field

swuehl
MVP
MVP

You will need to check the format of your UNIT_DATE, i.e. the exact definition of the DateTime code. I assume it's a UNIX time in millisecond, you can try something like this in your load script or chart:

=Timestamp(UNIT_DATE/(24*60*60*1000)+makedate(1970))

This will return a QV timestamp, formatted with your default Timestamp format code. You'll notice that the returned timestamp is off by 4 hours compared to your UNIT_TIME_STR, probably due to your UNIT_TIMEZONE used.

=Timestamp(UNIT_DATE/(24*60*60*1000)+makedate(1970)-maketime(4))

should correct for this (but you should double check how timezones and day light saving is handled in your source system, there are also QV functions that handle timezone calculations if needed).

If you want to derive year / month / day / dates etc. from the timestamp, just use any QV date / time function:

Year(UNIT_DATE/(24*60*60*1000)+makedate(1970)-maketime(4))

Regards,

Stefan

er_mohit
Master II
Master II

See the attached file

Qlik__Vinodh
Partner - Creator II
Partner - Creator II
Author

Hi mohit,

I will give u excel data .using that data ,provide me a year field.

in this data UNIT_DATE use as a date field.

Regards,

vindh

er_mohit
Master II
Master II

What type of output you want from that Which Year i have to calculate from .Explain it

swuehl
MVP
MVP

Pls find attached

rustyfishbones
Master II
Master II

You can use the UNIT_TIME_STR to get the Year Date

to get the date

DATE(DATE#(SUBFIELD(UNIT_TIME_STR,'_',1),'YYYYMMDD'),'DD/MM/YYYY') AS Date

to get the Year

YEAR(DATE(DATE#(SUBFIELD(UNIT_TIME_STR,'_',1),'YYYYMMDD'),'DD/MM/YYYY')) AS YEAR