Date field coming as General value in qlik

[Anonymous]

I have a Dates of each month in the column

When i am loading the date it is coming as a general value.

I tried changing the excel format to date. Still it is coming as a general value.

Please see the date image of the excel and QLik sense

[sunny_talwar]

Use The Crosstable Load to load your data and then format the date as date in a resident load

[Thiago_Justen_]

As stalwar1‌ said, you need to do something like this:

TEMP:

crosstable (Date, Value)

LOAD * from YOURDB;

FINAL:

LOAD

    *,

    DATE(FLOOR(Date)) As Date

Resident TEMP;

Drop Table TEMP;

[Anonymous]

stalwar1‌

I am already using cross table.

Kindly have a look at my script

jj:

CrossTable(Date1,Budget,6)

LOAD

    "Employee Code",

    Particulars,

    Speciality,

    "Distribution type",

    Distribution_Channel,

    "Year",

    "42736",

    "42767",

    "42795",

    "42826",

    "42856",

    "42887",

    "42917",

    "42948",

    "42979",

    "43009",

    "43040",

    "43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);

Load

Date(Floor(Date1)) as Date2

Resident jj;

Drop table jj;

[Thiago_Justen_]

Try converting date field like this

Date (Num# (Date1))

[sunny_talwar · Accepted solution]

Try with Num#() instead of Floor()

jj:

CrossTable(Date1,Budget,6)

LOAD

    "Employee Code",

    Particulars,

    Speciality,

    "Distribution type",

    Distribution_Channel,

    "Year",

    "42736",

    "42767",

    "42795",

    "42826",

    "42856",

    "42887",

    "42917",

    "42948",

    "42979",

    "43009",

    "43040",

    "43070"

FROM [lib://excel (qlik)/BUDGET1.xlsx]

(ooxml, embedded labels, table is Total);

Load

Date(Num#(Date1)) as Date2

Resident jj;

Drop table jj;

[Anonymous]

stalwar1‌

Thanks for the answer . It worked.

I tried using YTD script available on the community.

I am using this script for the YTD.

Sum({<Date2={'>=$(=(YearStart(AddYears(Max(Date2),0))))<=$(=addYears(Max(Date2),0)))'}>}Budget)/100000

I am not able to obtain YTD. Can you help me with this?

[sunny_talwar]

Try this

This Year YTD

Sum({<Date2={">=$(=Date(YearStart(Max(Date2))))<=$(=Date(Max(Date2)))"}>}Budget)/100000

LYTD

Sum({<Date2={">=$(=Date(YearStart(Max(Date2), -1)))<=$(=Date(AddYear(Max(Date2), -1)))"}>}Budget)/100000

[Anonymous]

stalwar1‌

YTD is not coming by using that expression.

My data consist of only 2017.

I tried both your expressions.

[sunny_talwar]

I see no reasons why they should not work... would you be able to share a sample where I can see it not working? or share images?