Count distinct "accumulated"

[#1]

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!!

[#2]

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

Count (Distinct Consumer)

Salu2

[swuehl · Accepted solution]

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

[#4]

Hi, the aggr function does exactly what I needed!

Thank you very much!

[#5]

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