10 Replies Latest reply: Sep 3, 2012 5:12 PM by Matt Johnson RSS

    pivot table totals

      I am working in a pivot table with an expression that is adding together the values in two different expressions.  The cell it being populated with the correct result, but the total for the column is zero it is not a dash but a zero.  all of the other columns are totaling OK.  I exported it to excel to confirm the correct total is not zero.

        • Re: pivot table totals
          Sander Janssen

          Hi, can you send an example qvw?

            • Re: pivot table totals

              The expression is:

               

              if([Tax unit of Property]='#N/A',[Total Value Replaced],if([Tax unit of Property]='Non-Stock item',[Total Value Replaced],0))

               

               

              Tax Unit of property is a dimension

              Total Value Replaced is another expression

               

              I tried to copy/paste the qvw file into this reply.......but couldn't (really new to qv).  The file is pretty large (2MB) which I can share if I can figure out how to attach.

               

              I may try to create a sample file that is a lot smaller while I wait for instructions on how to attach a file.  I will search here a bit more as well.

               

                • Re: pivot table totals
                  Michael Solomovich

                  Matt,

                  There is no total because there is no any aggregation function.  Try to put your whloe expression inside sum().

                   

                  Regards,

                  Michael

                   

                  Edit:  I think it should be like this:

                  sum(if([Tax unit of Property]='#N/A' OR [Tax unit of Property]='Non-Stock item',[Total Value Replaced],0))

                  • Re: pivot table totals
                    whiteline _

                    The problem is that your formula doesn't contain aggreagatoin.

                    When this expression evaluated for every value of [Tax unit of Property] it works.

                     

                    Imagine that it's calculating for the total row. Instead of [Tax unit of Property] you have all set of values.

                    It's an error and QlikView evaluate this as NULL.

                    Then NULL='#N/A' => FALSE, NULL='Non-Stock item' => FALSE => you get your '0'.

                      • Re: pivot table totals

                        I tried including the entire expression within a sum function like suggested but it returned a zero for all lines.

                         

                        Whiteline's comment makes sense to me I think (an accountant not a developer) why it works for each line, but not a total, but I am still unsure how to correct it.

                         

                        How do I attach a qvw file?  I know it is difficult to really address without seeing the entire picture.

                         

                        Thank you helping,

                        Matt

                          • Re: pivot table totals
                            Thomas Skariah

                            Hi Matt,

                             

                            For uploading a qvw first click on reply,go to Top(Right hand side) click on advanced editor,go the attachments and upload the file.Find the screenshots attached.

                             

                            Regards,

                            Tom

                              • Re: pivot table totals

                                Thanks for the file upload info.

                                 

                                Attached is the qvw file that contains the expression "Remove Non-Stock items and #N/A" which is working on a line by line basis but does not display a total.

                                 

                                Thanks in advance,

                                Matt

                                  • Re: pivot table totals

                                    Hi Matt,

                                     

                                    as whiteline mentioned above, the problem of the according expression is the missing aggregate-function. Without any of them, a summing up isn't possible. I rewrote your expression. It should work as I used two sums, each with your filter (if()) included.

                                     

                                    Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy],0))
                                    +
                                    Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy])/50185493*79974406,0)

                                     

                                    Regards

                                    Roland

                                     

                                     

                                    Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy],0))
                                    +
                                    Sum (if([Tax unit of Property]='#N/A' or [Tax unit of Property]='Non-Stock item', [Val/COArea Crcy])/50185493*79974406)

                                     

                        • Re: pivot table totals
                          whiteline _

                          Or type your expresison and say what are your dimnsions.