6 Replies Latest reply: Sep 23, 2016 4:30 PM by Michael Solomovich RSS

    Pivot Table Issue

    Mark Ritter

      I have an app that has a pivot table on one sheet.  When testing with a small amount of data it worked fine.

       

      It has 4 dimensions and 4 measures.  The measures are really just displaying some data elements.

       

      The data is in a qvd and is about 3M rows now.

       

      If I do not filter the data first the app just displays a circle (for loading) and shoots the memory use on the server up to 97%.  I'm not sure that it ever completes loading.

       

      Is there something that I need to do differently when using a pivot table to make it more efficient?  Is there an alternative to a pivot table that can give me the same output.  Or is this a bug.

        • Re: Pivot Table Issue
          Jonathan Dienst

          Its hard to give useful advice without some knowledge of the expressions and your data model. It could be one or more of several factors, such as

           

          • Inefficient expression logic
            • nested ifs
            • sum(if()) type operations
            • complex logic with pick(matches()) or other complex structures
          • Data model problems
            • broken or incomplete associations
            • overly deep nesting
            • multiple fact table(s) and large, high cardinality link tables
            • complex compound keys or synthetic key "runaway"

           

          A lot of these problems are mitigated by making selections, but as the model gets larger, the problems will become more severe.

           

          If you provide a small sample with some data reflecting the model structure and some chart objects with the offending expressions, it will be possible to provide more detailed assistance. Of course the small model may not reflect the performance problems, but it will help in looking for possible causes.

            • Re: Pivot Table Issue
              Michael Solomovich

              One more possibility - each of the pivot table dimension has a very large number of the distinct values.  So, it results in an enormous number of cells in the table.

                • Re: Pivot Table Issue
                  Mark Ritter

                  If that is the case how can I get around it?

                   

                  My dimensions are

                    Facility

                    Therapist

                    TreatmentDate

                    PatientName

                   

                  There are 500 Facilities.  Not sure how many Therapists.  Treatment Date is every day from January 2016 forward.  Don't know how many patients.

                   

                  I have been tasked to replicate an excel report into Qlik Sense to look as close as possible to the existing report.

                    • Re: Pivot Table Issue
                      Michael Solomovich

                      OK, probably thousands of Therapists and tens if not hundreds thousands of Patients.  The number of combinations is quite large.  It tells me that a table (pivot or not) is not a reasonable object here.  Unless you use a calculated condition that allows the table to calculate only if selections are made.

                        • Re: Pivot Table Issue
                          Mark Ritter

                          What is a reasonable object then?

                           

                          If I remove the date from the pivot table it then loads in a reasonable time.  But I need the date and the other data in the visualization. 

                            • Re: Pivot Table Issue
                              Michael Solomovich

                              I can't tell for sure what is a reasonable approach in this particular case, especially not knowing what is measured.  I can only recommend to try a set of charts/tables with fewer dimensions instead of one table with four dimensions.  And, keep this table with calculation condition, to see details when selections are made.
                              Just ask your users, do they really need an ability to see a table with many millions of cells.