Here is a good instruction on the CrossTable load:
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.
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, 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...
Ok. Your second sheet made the conversion irrevelant, but here is the script for the first sheet if needed sometime:
[150211 Only values Russia.xlsx]
(ooxml, embedded labels, table is Sheet1);
Date as [KPI],
Date(Num#(TempDate)) as Date,
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
- 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
So I am obviously still missing something... Can you please tell me where I'm still going wrong
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.