Formating Excel data inline with qlikview

[tinkerz1]

Hi,

The variable I have set in the QV load script is not matching the excel dates, so I am having difficulty concatenating the table.

I set the QV variable like this, I understand this to be in date format?

SET DateFormat='DD/MM/YYYY';

SET Month_of_Transaction='01/11/2015';

Then I concatenate the variable onto another table that is from excel 

$(Month_of_Transaction) as [Historical Dates]

The excel dates are left aligned to they look like text, so I use this calculated dimension to return a date 

=Date(num#([Historical Dates]),'DD/MM/YYYY')

But that turns my variable date to 31/12/1899

How can I set my variable date so when it is formatted in a calculated dimension it appears os 01/01/2015?

[MK_QSL]

What is the format of Date in excel?

[sunny_talwar]

Try this:

Date#($(Month_of_Transaction), 'DD/MM/YYYY') as [Historical Dates]

and then try the following dimension -> [Historical Dates]

[avinashelite]

try like this:

Date(Date#([Historical Dates]),'DD/MM/YYYY')

[tinkerz1]

It seems no get corripted as it passes through the formula in the QV calculated Dimension

=

Date(num#([Historical Dates]),'DD/MM/YYYY')

I have posted my QVW

[tinkerz1]

Its seems the dates from excel are converted to numbers in QV.

So 01/10/2015 becomes 42278

[avinashelite · Accepted solution]

Hi Neil,

check your data once it has some junk values  in the dates and then try as  below

try like this:

SET Month_of_Transaction=makemade(2015,11,01);

and use Date(Date#([Historical Dates]),'DD/MM/YYYY')

if it not working please share the sample data

NOTE: avoid the syntactic key

[#8]

it works very well. thanks