6 Replies Latest reply: Jun 6, 2014 11:55 AM by Juan Vitantonio RSS

    How to get the right total when using 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 because I used the same container many times.

      I want the total numbers of container in 2013 to be 4 because I used the same container 4 times in 2013. But I always get the same total number of containers in 2013 which is 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. And I do not want use the Count (DISTINCT [Job Ref])

      Does anybody have any idea?