topic Re: Timeseries in Excel - how to properly import in Visualization and Usability

Timeseries in Excel - how to properly import

Wed, 11 Feb 2015 14:25:37 GMT

I have a spreadsheet in Excel with monthly data for 14 variables. I have them in two different formats in two different worksheets:

Header 1	Header 2	Header 3	Header 4	Header 5
	Month 1	Month 2	Month 3	etc.
Variable 1	54	34	64
Variable 2	78	39	94
etc.

and

Header 1	Header 2	Header 3
	Date	Values
Variable 1	Month 1	54
Variable 1	Month 2	34
etc.	etc.	etc
Variable 2	Month 1	78
Variable 2	Month 2	39
etc.	etc	etc.

What I want to get out of it is something like this (this one is from Tableau, but I hope to create something similar in Qlikview - because I like the app/stroytelling idea. Can somebody help me? I'm also attaching the excel spreadhseet itself.

Thanks in advance!

-Stephan

Re: Timeseries in Excel - how to properly import

Gysbert_Wassenaar — Wed, 11 Feb 2015 14:43:48 GMT

See attached qvw.

Re: Timeseries in Excel - how to properly import

Wed, 11 Feb 2015 15:24:16 GMT

Wow that was fast! Thanks. I'll now try to figure out how to open that one from QV. Still - for my education (and maybe that of others too 😞 could you quickly walk us through the steps on how to do this? Thanks much.

Re: Timeseries in Excel - how to properly import

Anonymous — Wed, 11 Feb 2015 15:29:51 GMT

Here is a good instruction on the CrossTable load:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/24/crosstable

The second step is normally quite important and that is to convert the column headers to actual dates as they often end up as text or you might have to convert e.g. 201501 to 2015-01-31.

Kind regards

Niklas

Re: Timeseries in Excel - how to properly import

Wed, 11 Feb 2015 15:31:51 GMT

Ok, so I figured that out. Just copying the file into the data folder. But then now what? I still have the exact same thing as when I imported the Tableau sheet myself: I see the three columns - in which one has all of the variables I want underneath each other. But how do I now select those variables in the sheet? All I can select is Indicator, Date, and Percentage...

Re: Timeseries in Excel - how to properly import

Wed, 11 Feb 2015 15:33:36 GMT

Thanks Henric. I'm trying to put my teeth into that one too... Although it seems to me that my second worksheet HAD already 'transformed' my date. I used a little tableau-tool for that, that allows you to transform such cross-tables still within Excel. But so the problem I still have now is how to select the different values for my indicators that are now all in one column and have to be parsed out in such a way that I can select them as 'dimensions' in the graphing sheet (I guess). I guess what I haven't understood yet is this bit" "all qualifying fields must be listed before the attribute fields, and the third parameter to the Crosstable prefix must be used to define the number of qualifying fields."

Re: Timeseries in Excel - how to properly import

Anonymous — Wed, 11 Feb 2015 15:49:04 GMT

Ok. Your second sheet made the conversion irrevelant, but here is the script for the first sheet if needed sometime:

TempKPI:

CrossTable(TempDate, Value,1)

LOAD *

FROM

[150211 Only values Russia.xlsx]

(ooxml, embedded labels, table is Sheet1);

KPI:

LOAD

Date as [KPI],

Date(Num#(TempDate)) as Date,

Value

Resident TempKPI;

DROP Table TempKPI;

Once the data is in as three columns you can just start building your Chart:

New Sheet Object. e.g. Line Chart.

Use KPI and Date as dimension and put SUM(Value) as expression.

Add e.g. a multibox to be able so select just one KPI.

Re: Timeseries in Excel - how to properly import

Wed, 11 Feb 2015 16:03:35 GMT

Glad to see that it IS possible - if I could truly post something like yours (which is EXACTLY what I want) as an app on the internet, in such a way that

people can hover over the lines and see the precise values etc.
that they can also select individual indicators and
that I can then also start building a story line around the data - then that'd be awesome

But I'm not quite there yet. Here's what I get when I add the indicators and the dates as dimensions and the sum of the values as measure.

So I am obviously still missing something... Can you please tell me where I'm still going wrong

Re: Timeseries in Excel - how to properly import

Anonymous — Wed, 11 Feb 2015 16:21:34 GMT

Hi,

I am too mentally blocked to QlikView and did not realise that you work in Qlik Sense.

Here is my attempt to show what you want.

Please, notice that I have added two dimensions to the chart on the right hand side. I believe what is missing in your example is just the order of Date vs KPI/Indicator.

To the left hand side I have added a "Filter Pane" with your KPI-dimension.

Qlik Sense does not (yet) seem to have a "continous axis" so unfortunately a very long series of dates will show.

Kind regards

Niklas

Re: Timeseries in Excel - how to properly import

Wed, 11 Feb 2015 16:32:23 GMT

Yes thanks Niklas - that did indeed do the trick. But so I see that the viz is indeed not very intuitive (too long). And so I guess I'll have to try with the other program...