Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rhinoballs
Contributor II
Contributor II

Having Trouble with QV representing Dates as numbers?

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.

1 Solution

Accepted Solutions
sunny_talwar

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]);

View solution in original post

6 Replies
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;

rhinoballs
Contributor II
Contributor II
Author

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

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]);

View solution in original post

rhinoballs
Contributor II
Contributor II
Author

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

sunny_talwar

Awesome

dwforest
Specialist II
Specialist II

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).