5 Replies Latest reply: Jul 26, 2012 8:07 AM by paulina6735 RSS

    Sort a Pivot Table with blank fields as 0

    Richard Lucas

      I have a pivot table with 4 dimensions (customer, etc, etc) and two expressions, sales this year and sales last year.

      sales this year = Sum(SALES)

      sales last year takes the user date selections and collects the data from 1 year ago

      [CODE]Sum({<Year = {"$(=GetFieldSelections("Year")-1)"} >} SALES) [/CODE]

      There are some customers that do not have sales this year, but do have sales last year.

      My problem is when I try to sort the pivot table by sales this year (Sum(SALES), the customers that have no data in sales this year are automatically placed at the top of the list. They display a 0 value, but I think because the actual value in the cell is blank, Qlikview is not sorting them as 0 values.

      Does anyone know how I can fix this?

       

       

        • Sort a Pivot Table with blank fields as 0

          Rick

          You could try amending your expression for sales this year to deal with null values and convert to zero, something like:

           

          =IF(ISNULL(Sum(Sales)),0,Sum(Sales))


          Hope it helps,

            • Sort a Pivot Table with blank fields as 0
              Richard Lucas

              Thanks Nigel. I played with your idea. I had to remove the Null values in the Sales Last Year field, which worked.

               

              Sales This Year = Sum(SALES)




              Sales Last Year = If (ISNULL([Sales This Year]), NULL(),IF([Sales This Year]=0,NULL(), Sum({<Year = {"$(=GetFieldSelections("Year")-1)"} >} SALES)))


              This is not ideal because it excludes Customers with sales last year but no sales this year. I can live with this because I only need the top customers.

              I think the issue was the order in which the expressions were being calculated/populated - [SalesThis Year] first and [Sales Last Year] second, so whatever I seemed to specify in [Sales This Year] to exclude Null values had no effect, because they appeared when [Sales Last Year] was calculated.

              Not the cleanest of solutions, but it works for the moment!!!

                • Sort a Pivot Table with blank fields as 0
                  MManders

                  You should probably use indirect set analysis (use Help for syntax or one of the Set Analysis qvws)

                  Sample questions:

                  - select customers with sales from last year where they have sales this year

                  - select customers with sales from last year where they have no sales this year

                  - select customers with sales from this year where they had sales this year

                  - select customers with sales from this year where they had no sales this year

              • Re: Sort a Pivot Table with blank fields as 0

                For ones that are looking for the solution:

                I had a similar problem. The solution is to sort by expression and in this expression use rank function, for ex.

                rank(count(id)) and sort it ascending. This will solve the problem . It worked for me.