4 Replies Latest reply: Nov 25, 2014 9:00 AM by Juan Lopez RSS

    Count distinct "accumulated"

      Hi everyone,

       

      I have the following problem: I want to count the distinct values, grouped by date, BUT the distinct must be accumulated. Let me try to explain:

       

      Day 05/15: 5 new registers

      Day 05/16: 10 registers, but only 5 new (the other 5 registered in 05/15)

      Day 05/17: 10 registers, but 0 new (all them registered between 15 and 16)

       

      In SQL I would do something like:

       

      SELECT COUNT(DISTINCT c1.consumer), c1.date

      FROM registration r1

      WHERE NOT EXISTS (SELECT 1 FROM registration r2 WHERE r2.date < r1.date AND r2.consumer = r1.consumer)

      GROUP BY c1.date

       

      Thanks!!

        • Count distinct "accumulated"

          Hi if your consumer  are the key should be the next formulation in the object

          Count (Distinct Consumer)

           

          Salu2

            • Re: Count distinct "accumulated"
              Stefan Wühl

              In your script, you could do it in two steps ( I assume your register dates are of  QV date type with a numerical representation):

               

              LOAD

              Registers as NewRegisters,

              min(Date) as Date

              Resident YourRegistersTable group by Registers;

               

              This will create a table with new registers per date.

               

              You can just count these NewRegisters in a chart table expression grouped by Date or do similar in the script then (in another load).

               

              or, as a pure front end solution and still sensitive to some selections in other fields:

               

              Create a chart with dimension Date (but this Date's load order needs to be chronological) and as expression:

              =count(aggr( Registers, Registers))

               

              The implicite distinct qualifier of the aggr() function will do the job needed here.

               

              Hope this helps,

              Stefan

              • Re: Count distinct "accumulated"
                Juan Lopez

                Iván, esto no tiene en cuenta el comportamiento que él quiere con los días, la idea es que

                 

                en el primer día estén los registros de ese día

                en el segundo estén los del primero MAS los NUEVOS del segundo

                en el tercero, los del primero, MAS los nuevos del segundo, más los nuevos del tercero...

                 

                y así

                 

                estoy trabajando en esto y creo que he llegado a una solución, estoy haciendo pruebas, si algo la posteo al rato