Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
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
Hi if your consumer are the key should be the next formulation in the object
Count (Distinct Consumer)
Salu2
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
Hi, the aggr function does exactly what I needed!
Thank you very much!
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