1 Reply Latest reply: Oct 26, 2010 5:36 PM by Karl Pover RSS

    PIVOT Sum Across Columns

      I have a pivot table with Salesperson down the left side and products going across the top.

      The expression I used was a basic 'COUNT (PRODUCTS)' for the first iteration.

      I clicked on all the partial sums in the PRESENATION TAB and they add up nicely across rows and columns.

      I then change the expression to calculate the value of the PRODUCTS sold by changing the expression to:
      'COUNT (PRODUCTS) * VALUE' where VALUE is the dollar price of each type of PRODUCT.

       

      The partial sums still work with the ROWS...(i.e. I see a sum at the bottom), but the sum of the COLUMNS stopped functioning and I can't get them to simply add up going ACROSS.

      Any way to fix this?

       

      Thanks in advance!!!

        • PIVOT Sum Across Columns
          Karl Pover

          The partial sums stopped working because there are more than one value in VALUE per product so just calling VALUE returns null. You have to tell QlikView how to treat mulitple values in VALUE. You can do a sum, avg, count, etc. You can also try the aggr() function that evaluates an expression while respecting a dimension. That might be your best bet here so try changing the functions to:

          sum(aggr(COUNT(PRODUCTS)*VALUE),PRODUCT))

          Regards.