Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
DS14
Partner - Contributor III
Partner - Contributor III

Change Date format from Text to Date

Hi Experts,

I have to change the format of year column from text to date  format. I have the Year data in following format.

Fixed -Pay %Varaible -Pay %Pers.No.Year
00100003982019-20
00100004422019-20
00100004472019-20
00100004542019-20
00100004772019-20
0.0011104870.196100604100000012019-20
0.006473060.230132449100000222019-20
0.0072629280.188228022100000572019-20
0.0087155130.281253869100001332019-20
0.0102820580.327032225100000392019-20
0.011166930.10854293100004462019-20

 

I need the output year column as date format and in year column as date format  2019-2020.

Please suggest .

 

Labels (4)
4 Replies
Ivan_Bozov
Luminary
Luminary

Hello! How about this:

DATE(DATE#(LEFT(Year,4),'YYYY'),'YYYY') & '-' & DATE(DATE#(RIGHT(Year,2),'YY'),'YYYY') AS Year

 

vizmind.eu
DS14
Partner - Contributor III
Partner - Contributor III
Author

& '-' & using this the format is still in text. It's not in number format.
Ivan_Bozov
Luminary
Luminary

You can of course separate that

DATE(DATE#(LEFT(Year,4),'YYYY'),'YYYY') AS Year1,
DATE(DATE#(RIGHT(Year,2),'YY'),'YYYY') AS Year2

and then use the following as dimension in your chart

Year1 & '-' & Year2

 

vizmind.eu
tresesco
MVP
MVP

Your one year field value is '2019-20' and you want this value to be in date format ? Could you explain a bit more. Usually in such financial year representation it is recommended to use dual(), may be something like:

Dual(YearField, Left(YearField,4)) as Year 

This will give you value (text at at the UI) '2019-20' and a numeric value 2019 at the underlying value you can perform mathematical operation on.