Having Trouble with QV representing Dates as numbers?

[Anonymous]

Hi,

I have a simple Month column which shows MMM YYYY within an excel sheet; however when I transpose this data into QV as a Dimension it shows the data as numbers. For example Sep 2017 is shown as 42979, Oct 2017 is shown as 43040, and so on.

I cannot override this data to show Months rather than these numbers. Does anyone know why this month data is being shown as numbers, and more importantly how can I change it within QV to show the dates instead of these numbers?

The excel sheet is attached for info, and I'm using the data from the TAB named Resources Profile Data.

[sunny_talwar]

When you say transpose, I am guessing you are doing a CrossTable load? If you are then you can fix this in the next resident load like this

Table:

CrossTable(MonthYear, ....)

LOAD ....

FROM ...;

FinalTable:

NoConcatenate

LOAD Date(Num#(MonthYear), 'MMM YYYY') as MonthYear

            Otherfields....

Resident Table;

DROP Table Table;

[Anonymous]

Hi Sunny, hope your Ok.

I'm simply input this data via Table Files in QV as follows;

LOAD [Duration (Days)],

     Month,

     [Kier Services | Highways],

     [Kier Enterprise],

     [Kier Supply Chain],

     [Task Total Days]

FROM

(ooxml, embedded labels, table is [ Resources Profile Data]);

Its the simple Month in the Load above from the Excel file that is causing me the problems. Excel shows this data as a date, but QV is showing it as a number.

Does this make sense, sorry if it doesn't?

[sunny_talwar · Accepted solution]

May be try to wrap it around with Date() function with your required formatting?

LOAD [Duration (Days)],

    Date(Month, 'MMM YYYY') as Month,

    [Kier Services | Highways],

    [Kier Enterprise],

    [Kier Supply Chain],

    [Task Total Days]

FROM

(ooxml, embedded labels, table is [ Resources Profile Data]);

[Anonymous]

Sunny, again you are a star!!!! It worked. Thank you

[sunny_talwar]

Awesome

[dwforest]

Just to add, there seems to be issues with dates when you load data from different sources. It seems only the first connection's dates get formatted and the rest need to be manually changed.

It can also happen if the date format in the source does not match the format string set in Qlik Sense (not sure if this applies to QV, but scripts seem to behave the same).