Qlik sense desktop date question

[#1]

HI,

I have an excel sheet with some dates as headings from excel

Project	01/01/15	01/02/15	01/03/15
Project 1	100		50
Project 2		200	50
Project 3	300

I have been able to import the table and get the totals to sum correct, however how can I get the dates to show as Jan,Feb,Mar (not 01/01/15, 01/02/15.

Many Thanks

[Josh_Good]

Hi Michael,

In order to do this you need to 'uncross' the table so you have a single row for each piece of data.  To do this you will use the "CrossTable" function before the load statement.  I have given a quick example below (I assume your data was "Budget" info).

CrossTable(Dates, Budget)

LOAD Project,

    [1/1/2015],

    [1/2/2015],

    [1/3/2015]

FROM

(ooxml, embedded labels, table is Sheet1);

The results of using the CrossTable function will transform your data when it is loaded into the format below.

-Josh

Qlik

Project	Date	Budget
Project 1	1/1/2015	100.00
Project 1	1/2/2015
Project 1	1/3/2015	50.00
Project 2	1/1/2015
Project 2	1/2/2015	200.00
Project 2	1/3/2015	50.00
Project 3	1/1/2015	300.00
Project 3	1/2/2015
Project 3	1/3/2015

[#3]

HI Sorry, not sure I explained my self correctly.

From the table I had above I would like to produce a graph that shows

Jan = 400

Feb = 200

Mar = 100

At the moment the graph dimensions show

01/01/15 = 400

01/02/15 = 200

01/03/15 = 100

Thanks

[Anonymous]

It's just a date formatting. Function month(Date) returns month name.

[Josh_Good]

Hi Michael,

You will still need to uncross the table as describe above.  You can then use the Month function (in the UI or in the script) to convert the dates over to months.

Note, pulling dates from Excel can be a bit tricky because Excel may think it is a date or may think it is a text string.  If you are getting an error Excel is likely telling Qlik Sense that your dates are text strings.  If you have trouble here feel free to post your app and I will take a quick look help you along.

-Josh

Qlik

[#6]

Sorry I'm new to sense desktop, where do I find the formatting,

I can format the Measure but not the dimension, do I need to add the formatting on the data load?

[Anonymous]

It is always a good idea to have it in the script.  If you need only month name:
month(Date) as MonthName
For month and year:
date(monthstart(Date), 'MMM-YY') as MonthYear
Typically I have both

[#8]

Hi thanks for this, my script loads like this

crosstable(month,value)
LOAD
    Status,
    "01/10/2014",
    "01/11/2014",
    "01/12/2014",
    "01/01/2015",
    "01/02/2015",

Where would I add the month(Date) as MonthName

[Anonymous]

I can add this after crosstable.  An example attached.

I had to use inline load to replace your excel load which I don't have.  The important part is the at the end of the script.

[Anonymous]

That's correct Josh, I had to use date#() in the example to convert string to date.