8 Replies Latest reply: Jun 6, 2014 10:56 AM by sarvjeet singh RSS

    How to use Count and Distinct over period?

    Darri Hilmarsson

      Hi, QV community

       

      I have little problem that needs solution.

      I have this data and I want to count numbers of Containers

       

      Job RefContainer NumberDescriptionYearMonth
      REYE507SANU421Fish2013may
      REYE507SANU421Meat2013may
      REYE604SANU421Car2013april
      REYE604SANU421Food2013april
      REYE610SANU421Frozen fish2013jan
      REYE610SANU421Meat2013jan
      REYE612SANU421Car2013mar
      REYE612SANU421Food2013mar

       

      as you can see, in this data there is two lines for every Job Ref.

      So I use the expression Count (DISTINCT [Container Number]) so that QV don't double counts the containers in every Job Ref.

      I get the right numbers of containers in the Job Ref which is 1.

      But the problem is when want to know how many containers where in 2013.

      I want the numbers of container in 2013 to be 5 but I get 1.

      I now that is because I use DISTINCT. I only now that if I use the sow of Rows I get the right answer.

      I want to have Pivot table so I have to change the expression.

      Does anybody have any idea?