Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Reformatting output from Crosstable loads

Hi,

I have been trying to import and concatenate together 4 different sales plans so that they can be analyzed in Qlik and I have run into a minor formatting issue.

All of the plans are formatted similar to the below where each row has some attributes (SKU, Customer, Description, etc), and then there are a series of columns with Date headers showing when those amounts are expected.

Attribute1Attribute2Attribute3MMM-YYMMM-YYMMM-YY
Attribute 1Attribute 2Attribute 3AmountAmountAmount

So I've successfully used crosstables to translate these sheets into tables and stitched them all together using concatenate.

The problem that I am having now is that the Date headers were loaded as numbers.  And I can't figure out how to reformat them back into dates.  I've tried using date() and date#() in the crosstable load.  I've tried to use date() and date#() in the resident loads that I used to concatenate all the sheets together.  I've also tried using date functions in the vizualizations and nothing seems to be working.

Nothing I try has been able to translate the ##### number format for dates into the MM/DD/YYYY format that I have set as default.

Would anyone have ideas why I can't get this to work?  Is this a weird quirk of crosstables?  Are those values a special data type since they used to be field names?

Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this

Date(Num#(MonthYearFieldName)) as MonthYear

View solution in original post

2 Replies
sunny_talwar

Try this

Date(Num#(MonthYearFieldName)) as MonthYear

Anonymous
Not applicable
Author

This worked, thanks!