Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Reformatting output from Crosstable loads


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.

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?


1 Solution

Accepted Solutions

Try this

Date(Num#(MonthYearFieldName)) as MonthYear

View solution in original post

2 Replies

Try this

Date(Num#(MonthYearFieldName)) as MonthYear

View solution in original post

Contributor II
Contributor II

This worked, thanks!