Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dashboard Error

Hey guys,

I have one excel file which has date in format of 'MM/DD/YYYY' and one txt file in which date is in format of 'YYYY/MM/DD' .

So can you please tell me how to show these two date in 'DD/MM/YYYY' in QV DashBoard?

Thanks,

Amit

6 Replies
its_anandrjs

Hi,

Convert this date formats in the load script and

Ex:-

Load

Date(Date#(DateField1,'MM/DD/YYYY'),'DD/MM/YYYY') as DateField1

From Excel

Load

Date(Date#(DateField2,'YYYY/MM/DD'),'DD/MM/YYYY') as DateField2

From txt

Or you can set date format also

SET DateFormat='DD/MM/YYYY';

Regards

Anand

Not applicable
Author

date.JPG.jpg

My dateformat is in 'MM/DD/YYYY' but still it is coming as the above

Anonymous
Not applicable
Author

Hi!, try with function Alt and put it all in ne same format. Once you do that, it'll pick up the environment format.

alt( date#( DateField1, 'YYYY/MM/DD' ),

date#( DateField1, 'MM/DD/YYYY' ),

date#( DateField1 , 'MM/DD/YY' ),

'No hay fecha válida' )

alt( date#( DateField2, 'YYYY/MM/DD' ),

date#( DateField2, 'MM/DD/YYYY' ),

date#( DateField2 , 'MM/DD/YY' ),

'No hay fecha válida' )

Then turn it into num and that should be it.

Regards!

its_anandrjs

Hi,

Load this ways and this is example

ExcelLoad:

LOAD Date#(DateField1,'MM/DD/YYYY') as DateField1,

     Date(Date#(DateField1,'MM/DD/YYYY'),'DD/MM/YYYY') as NewDateField1;

Load * Inline [

DateField1

11/25/2014 ];

TxtLoad:

LOAD Date#(DateField2,'YYYY/MM/DD') as DateField2,

     Date(Date#(DateField2,'YYYY/MM/DD'),'DD/MM/YYYY') as NewDateField2;

Load * Inline [

DateField2

2014/11/22  ];

And you get this fields

dates.png

Provide any sample file then it will easy

Note:- also did you check there is multiple date formats in the single field if so then use the Alt function

Ex:-

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Let me know

Regards

Anand

jyothish8807
Master II
Master II

Hi Amit,

Try using a Trim function, i think there are some extra space in you excel sheet.

Regards

KC

Best Regards,
KC
giacomom
Partner - Contributor III
Partner - Contributor III

Hi,

if you are using a Table Box you can set the date format in the Number tab of the chart properties window:

changeDateFormat.PNG.png