Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Qlik sense desktop date question

HI,

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

Project01/01/1501/02/1501/03/15
Project 110050
Project 220050
Project 3300

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

9 Replies
Employee
Employee

Re: Qlik sense desktop date question

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

ProjectDateBudget
Project 11/1/2015100.00
Project 11/2/2015
Project 11/3/201550.00
Project 21/1/2015
Project 21/2/2015200.00
Project 21/3/201550.00
Project 31/1/2015300.00
Project 31/2/2015
Project 31/3/2015

Not applicable

Re: Qlik sense desktop date question

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

mov
Esteemed Contributor III

Re: Qlik sense desktop date question

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

Employee
Employee

Re: Qlik sense desktop date question

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

Not applicable

Re: Qlik sense desktop date question

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?

mov
Esteemed Contributor III

Re: Qlik sense desktop date question

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

Not applicable

Re: Qlik sense desktop date question

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

mov
Esteemed Contributor III

Re: Qlik sense desktop date question

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.

mov
Esteemed Contributor III

Re: Qlik sense desktop date question

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