Skip to Content
Author's profile photo Former Member

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


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 :

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.



Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Piet Jonkers
      Piet Jonkers

      excellent solution!