Vintage Analysis in QlikView

[shumailh]

Hi,

I am doing month on month analysis and the data present on the database is completely in a de-normalized form for the quick access of data. This table has 400K records and more than 40 fields. I am currently working on Status field which is changed on monthly basis i.e. A or P or S. The denormalization is performed in such a manner that on monthly basis i have created a new field and update all the status on it. The field name is look like {Month}AP{Year} and I update it with each record status (A or P) monthly. I am facing problem while i am going to perform trend analysis on it.

Can I use these fields for the trend analysis (Line Chart) on QlikView?

[#2]

The easiest solution would likely be to normalize the data in the load script.

Probably the best way to do this is to load a seperate table from the same data which has one or several keys to join to main data, and a date column which you calculate, one or more value columns.

[#3]

Yes, You can.

If you have Date Dimension in a different field (Month/Year) then for the expression, you could use the substring function to get A/P Value and its count etc.

If you provide a sample file with the image of what chart you want to see in the model, that would help.

[shumailh]

Dear Vidyut!

No there is no date dimension in the table. I am using the field name as date dimension like JanAP08, FebAP08, MarAP08,.... and this field contains A / P value. Below is the sample grid and graph for your reference,

[#5]

Hi,

This actually is a cross table. So why don't you convert it to a straight table using the 'CrossTable(MonthYr,APValue) ' and link it to the main table using the common key.

This way all the fieldNames will come in the 'MonthYr' field ( You can process it further to delete the AP in the string ), and the A/P Value coming as corresponding Value.

The tables should be defined so that the analysis is easy. In this case, using crosstable will provide you a better/easy model, IMHO

Best Regards,

Vidyut