topic Re: Timeseries Data in Columns from Excel in QlikView

Timeseries Data in Columns from Excel

Sat, 18 May 2013 17:37:04 GMT

Hi,

I am a real newbie with Qlikview.

I am just trying it for the first time, but im struggling to chart some timeseries data from excel.

My data in excel has monthly columns. For example, i have regular headers such as KPI Name, KPI Code etc, but then from column H onwards it is my data. So Column H is April 11, Column I is May 11, and column J is June 11. This carries on until April 13.

I can bring my data into Qlikview, but i cannot get a chart to show, for example, row 5 (Monthly Results) across all months.

Basically i want to show the monthly results from April 12 to March 13 in one line chart.

Please note my data has to remain as it is, as it is full of links to external workbooks etc..

Any help would be much appreciated!!!

Thanks

Paul

Re: Timeseries Data in Columns from Excel

swuehl — Sun, 19 May 2013 15:12:08 GMT

If you say 'my data has to remain as it is' I believe this relates to the format of your input data source, the excel file, right?

You can leave your excel file untouched and try transforming your crosstable to a straight table in the QV load script, like this:

INPUT:

LOAD * INLINE [

KPI, KPIName, Jan 11, Feb 11, Mar 11

1, KPI1, 100 ,111, 99

2, KPI2, 80,100, 120

];

TRANS:

CrossTable (Month, KPIValue, 2) LOAD * Resident INPUT;

drop table INPUT;

RESULT:

NOCONCATENATE LOAD KPI, KPIName, Date#(Month, 'MMM YY') as Month, KPIValue Resident TRANS;

drop table TRANS;

Now you can use Month as a dimension in your charts, see also attached.

Regards,

Stefan