topic Re: Dashboard Error in QlikView

Dashboard Error

Tue, 25 Nov 2014 08:16:29 GMT

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

Re: Dashboard Error

its_anandrjs — Tue, 25 Nov 2014 08:23:44 GMT

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

Re: Dashboard Error

Tue, 25 Nov 2014 08:40:46 GMT

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

Re: Dashboard Error

Anonymous — Tue, 25 Nov 2014 08:48:11 GMT

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!

Re: Dashboard Error

its_anandrjs — Tue, 25 Nov 2014 08:57:48 GMT

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

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

Re: Dashboard Error

jyothish8807 — Tue, 25 Nov 2014 08:57:59 GMT

Hi Amit,

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

Regards

Re: Dashboard Error

giacomom — Tue, 25 Nov 2014 09:07:03 GMT

Hi,

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