To bring out the date format unique

[nevilledhamsiri]

If I am to make date format under all three date formats, how it should be done. Also at times, the date loaded appearing as a value. When that happen how that covert in to a correct date format

Thanks

Neville

[vvira1316]

Have you tried following?

DateTbl:
LOAD date#(date1, 'DD-MM-YYYY') as Date1,
date#([date-2], 'MM/DD/YYYY') as Date2,
date#([date-3], 'DD/MM/YYYY') as Date3,
Amount
FROM
[DEBTOR_AGE.xlsx]
(ooxml, embedded labels, table is Sheet1);

[vvira1316]

I think you are looking for following....

Load as follows

DateTbl:
LOAD AutoNumber(RowNo()) as RowNo,
date#(date1, 'DD-MM-YYYY') as Date1,
[date-2] as Date2,
date#([date-3], 'DD/MM/YYYY') as Date3,
Amount
FROM
[DEBTOR_AGE.xlsx]
(ooxml, embedded labels, table is Sheet1);

and use Number Tab on chart as seen in below image

[nevilledhamsiri]

Hi Vijay,

May be I find difficult to add the date formatting as per the snapshot you attached. When the number tab is clicked  on the expression only appear not the dimension such as Date1, 2 & 3, May be the way I proceed is not correct. Con you please explain a bit on this. Also, You had suggested two data loading, once 1st loading was done, Data_2 still appeared as a value. Also under 2nd loading instead 1st, for date_2, you have not brought the same formatting we did for 1 & 3 dates . Could you please say why it is so?

Thanks

Neville

[vvira1316]

Hi,

In first load I used all three date fields as dimensions. Yes for dimensions you can't use Number tab of chart property. the second date field is as per US format so Qlik recognized it without the need to explicitly format like the other two. In order to display as date you may have to play with it in the front end / may be explore default date settings in Qlik and/or System.

In the second load I wanted to use three date fields as expression to utilize the number tab on the chart property and set the format manually.

I hope it clarifies.

BR,

Vijay

[nevilledhamsiri]

Thanks Vijay

Neville