Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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
It's just a date formatting. Function month(Date) returns month name.
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
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?
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
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
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.
That's correct Josh, I had to use date#() in the example to convert string to date.