How to implement a Running Count Distinct
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
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 :
resulting in this :
Running Count Distinct
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.