2 Replies Latest reply: Jul 31, 2013 1:06 PM by Ali Gracie RSS

    Pivot table set analysis - Ignoring dimension under specific condition

      Hi,

       

      I have an issue - not sure if it's set analysis or something else that's gonna solve the issue.

       

      Lets say my data set is just one table with these columns:

      Company

      Quarter

      ProductID

      DateSold

       

      I can see how many different types of products were sold by each company in a straight table like this:

       

      CompanyTypes Sold
      QrikView10
      ABM18

      The expression would be =count(distinct ProductID).

       

       

       

      Here comes the problem: I want to create a pivot table like this:

      CompanyQuarterTypes SoldTotals Sold%
      QrikViewQ11225%
      Q22229%
      Q332214%
      Q442218%
      ABMQ132214%
      Q242218%
      Q352223%
      Q462227%

      How do I get the totals to show up the same as the straight table above? Basically I want that column to ignore the Quarter dimension so that I get 10 for QrikView and 18 for ABM. (1+2+3+4=10,    3+4+5+6=18).

       

      Types Sold would have the same expression. Count(Distinct ProductID).

      But to get the Total Sold I can't get what I'm getting in the straight table.

      The Totals Sold column above is using this expression: Count({<Quarter=>} total ProductID). I've tried various things that don't seem to work.

       

      I've attached sample QVW.

       

      Thank you for reading this.