6 Replies Latest reply: Jul 1, 2011 2:38 PM by kiwichickny RSS

    Variance analysis for same data



      I have 2 sets of data each of size 10 MB Excel file. One file is as of Week 1 and other is as of Week 2. I was able to create 2 separate qlikview files for these 2 sets of data.

      I now need to identify how a particular value has changed from week 1 to week 2.

      What is the best way to get a comparative view of 2 or more weeks in one file.

      One way could be combine both the file and put one additional column which captures week. But this means the data volume would be huge say 2 months down the line.


      Is there any other way to resolve this?

        • Variance analysis for same data
          John Duffy


          Can you post a sample application with a small set of Week 1 and Week 2 data build through an inline load. Only include the fields required for the comparative view. Also, include a brief description of the desired result.


          • Variance analysis for same data
            John Witherspoon

            If there's only one field that you care about how it changes from week to week, you don't need a huge data volume to store it. Let's say your weekly file currently has this data:

            KeyField, Field1, Field2, Field3, FieldToMonitorWeekly

            I'd break that into two different tables:

            KeyField, Field1, Field2, Field3

            KeyField, Week, FieldToMonitorWeekly

            Not sure I've correctly understood your question, though.

              • Variance analysis for same data
                Tresesco B

                Hi Naveen,

                If i have understood your problem, you can try using an extra field for WEEK in the files separately (in both the files) and then try analysis the data taking this WEEK field as flag (in if condition or SET). This would also let you avoid the problem of hugeness of the file.



                  • Variance analysis for same data

                    Hi Tresesco


                    I think you have understood my problem well. But can you help me with the exact solution?

                    I understood the part where i have to create an extra field in each of the file.

                    How exactly can i link both the files in one Qlikview document so that if i select week 1 then week 1 data is displayed and if i select week 2 then week 2 data is displayed.

                    I am pretty new to this application hence not able to understand SET condition that you are talking about. Can you please help me with step by step solution for the same if possible ?


                    Thanks in advance


                    Hi John Thanks for your attempt as well.. There are many data points ( around 20) or so which changes every week so yes i understood that to that extend the volume would be reduced...but file volume would be a challenge say 4 months down the line if not 2 months.




                      • Variance analysis for same data
                        Tresesco B


                        i think it would be easier to reach to your solution, if you could upload your sample excels. upload the excel with all fields you need and some sample data.

                        regards, tresesco

                          • Variance analysis for same data



                            I'm trying to do something with variance too - in particular schedule and budget variance. I have to show this variance according to a project manager - which I think I have figured out how to do. But, I don't know how to do it in terms of day, week, month. I have a field called snapshotdate and that's all I have to work with. Do I have to go into the listbox or table properties and do an sql expression that turns the date into the three different fields I want? please let me knwo if I'm on the right track. I would greatly appreciate it. thank you.