Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
See attached qvw.
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.
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
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...
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."
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.
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
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
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
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...