3 Replies Latest reply: Jun 23, 2011 6:11 PM by Roland Kunle RSS

    Pivot table sum not correct/consistent

      Hello everyone,

       

      i have a small but confusing problem with my qlikview pivot table.

      I created a pivot table with several expressions, but i do not get the correct partial sums of it.

      the first expressions sums are correct, but the "Pot. Absatzmenge" and "Absatz Gesamt" do not sum in some cases. Some sums are correct, some are not.

       

      Please see attached qvw file.

       

      Hope you can help me finding the problem.

       

       

      Thanks in advance

        • Re: Pivot table sum not correct/consistent
          Nagaian Krishnamoorthy

          Change the expression definition for "Pot. Absatzmenge" and "Absatz Gesamt" to include Sum function to see the partial sum values.

           

          Modified qvw file is attached.

           

          Hope this helps.

            • Re: Pivot table sum not correct/consistent

              Wow, that was fast! Thanks for your answer, but i it's not the correct sum.

               

              The expression "GPs" contains a concat in order to avoid multiple rows for each Object. I attached to file where you can see the "GP Nummer" to each Object. If an Object has four "GP", it has one row for each gp, but the "Pot. Absatzmenge" and "Absatz Gesamt" are only once for each Object.

              When there is sum() used in the expressions, qlikview summarises the four value (which are all the same)...

               

              hope you can help me

                • Pivot table sum not correct/consistent

                  Hi d.drechsler,

                   

                  there are two things to mention:

                  - QV sums up correctly when using the sum()-function within the expression. And it is also correctly showing the '-' for a partial sum that can't be build without any aggreation function in the according expr.

                  As a workaround you can use the avg() --> "avg([OBJEKTE_GP_AP.Potentielle Absatzmenge])". This will work as expected because the values are all the same for one Object. And then QV can build the partial sums correctly.

                  But:

                  - This is a workaround! For my oppinion the data modell and/or the load-script isn't correct. It seems that the values of "Potentielle Absatzmenge" are too often in the table (Redundancy). Or in a wrong level of a hierarchy I do not know (I guess QP instead of Object). There you should start thinking about a redesign . . .

                   

                  HtH

                  Roland