4 Replies Latest reply: May 21, 2009 7:23 PM by vidyut.verma RSS

    Vintage Analysis in QlikView

    Shumail Hussain

      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?

      [Code]

      [/Code]

       

        • Vintage Analysis in QlikView
          ewasserman

          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.

          • Vintage Analysis in QlikView
            vidyut.verma

            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.

              • Vintage Analysis in QlikView
                Shumail Hussain

                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,

                 







                  • Vintage Analysis in QlikView
                    vidyut.verma

                    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