Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Timeseries in Excel - how to properly import

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

Header 1Header 2Header 3Header 4Header 5
Month 1Month 2Month 3etc.
Variable 1543464
Variable 2783994
etc.

and

Header 1Header 2Header 3
DateValues
Variable 1Month 154
Variable 1Month 234
etc.etc.etc

Variable 2

Month 1

78
Variable 2Month 239
etc.etcetc.

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

1 Solution

Accepted Solutions
hedborg
Contributor

Re: Timeseries in Excel - how to properly import

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

9 Replies

Re: Timeseries in Excel - how to properly import

See attached qvw.

Not applicable

Re: Timeseries in Excel - how to properly import

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.

hedborg
Contributor

Re: Timeseries in Excel - how to properly import

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

Not applicable

Re: Timeseries in Excel - how to properly import

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...

Not applicable

Re: Timeseries in Excel - how to properly import

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."

hedborg
Contributor

Re: Timeseries in Excel - how to properly import

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.

Not applicable

Re: Timeseries in Excel - how to properly import

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

hedborg
Contributor

Re: Timeseries in Excel - how to properly import

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

Not applicable

Re: Timeseries in Excel - how to properly import

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...