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
      REYE610SANU421Frozen fish2013jan


      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?