1 Reply Latest reply: Jan 10, 2013 1:14 PM by Stefan Wühl RSS

    Distinct Count of non zero records in pivot table

      Hi Community,

       

      I have a Pivot chart that I need to generate a distinct count of values that are in the VERTICAL columns.  I can get the Pivot chart to distinct count across rows but not columns.  Has anyone run across this issue before?  I have attached a sample qvw.  In the image below, you can see that I am successful in counting the number of distinct (non zero) values across rows.  I am not able to achieve the same result vertically.  What happens is, the chart does a sum across the column as opposed to a distinct count of non zero values. 

       

      vertical sku.jpg

        • Re: Distinct Count of non zero records in pivot table
          Stefan Wühl

          You can use Dimensionality() to check for the vertical total line:

           

          =if (SecondaryDimensionality() <> 0,

          if(Dimensionality() <>0,

                    sum({$<Year={$(=Only(Year))}>} Depletion),

                    count(distinct [Store Number])),

             

          count(distinct {$<Year={$(=Only(Year))}>} if(aggr(sum({$<Year={$(=Only(Year))}>} Depletion),%OutletKey, SKU) > 0, SKU))

           

          )