Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (1)
6 Replies
its_anandrjs
Not applicable

Re: Dashboard Error

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

Re: Dashboard Error

date.JPG.jpg

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

daiana_rossignol
Not applicable

Re: Dashboard Error

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
Not applicable

Re: Dashboard Error

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
Not applicable

Re: Dashboard Error

Hi Amit,

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

Regards

KC

Best Regards,
KC
giacomom
Not applicable

Re: Dashboard Error

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