How to implement a Running Count Distinct

[Former Member]

UPDATE - Attached is a .wid file , renamed to .txt so I coulkd attach it, with the proposed solution. Is's on BO 4.1 SP5. In order to work, rename it back to .wid

Hi,

There was a particularly tough question (at least for me) posted by michal.radomski in WEBI Running Count Distinct with tooke me some time to figure out how to solve it.

The question was how to implement a RunningCount of distinct elements The OP had a list of IDs and dates and he needs to count the distinc elements over time (a date), his data is somethong like this :

date	ID
01/01/2009	1
01/01/2009	2
01/01/2009	3
01/01/2009	4
01/01/2009	1
01/01/2009	5
01/01/2009	2
01/01/2009	6
02/01/2009	3
02/01/2009	7
02/01/2009	8
02/01/2009	9
02/01/2009	2

resulting in this :

date	Running Count Distinct
01/01/2009	6
02/01/2009	9

In order to do so,I proposed a solution using the following formula , which I´ll try to explain

[Running Count Distinct]  =RunningSum(Sum(If ( ([Id] ForEach([Id];[date])  In ([Id];[date]) = Previous([Id] ForEach ([Id];[date]) )) In ([Id];[date]);0;1) ) )

The idea behind this is compare , row by row, The [id] element with its previous [id] object  AS IF IT WAS ORDERED BY ID INSTEAD OF DATE , in

case it´s equal  I mark the row with 0 otherwise 1. In this manner, when I sum up the values aggregated by date I get the number of different IDs over time.

As the data is, in fact ordered by [date], I force the comparison to be evaluated as if was ordered by [id] and redefine the context to include [id].

This is accomplished by setting the correct order in the redefinition context ([ID];[date]).

In fact, when redefining context, the order in which the objects are set matters in terms of the final result.

Have ypu all a nice WEEK END.

Regards,

Rogerio