Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anonymous
Not applicable
Author

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

View solution in original post

9 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Anonymous
Not applicable
Author

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
Author

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
Author

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

Anonymous
Not applicable
Author

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
Author

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

Anonymous
Not applicable
Author

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
Author

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