WEBI Running Count Distinct

[Former Member]

Hi,

I need to calculate YTD count distinct by time. My data is

date    id

1/1/2015    1

1/1/2015    2

…   

1/2/2015    1

1/2/2015    3

and the report is:

date	# of id	# of id YTD
=[date]	=count([id];distinct)	???

I've tried several versions of =Count([id] Where ([id] dynamic condition);Distinct)  ForAll([id]), but with no luck (Dynamic condition is going to extend the scope incrementally)

I'd much appreciate any advise on this.

Thanks,

Michal

[Former Member · Accepted solution]

Hi Michal,

Try

=RunningSum( Count ([id]) ) for "# of id YTD"

and check.

Regards,

Yuvraj

[Former Member · Accepted solution]

hi,

this gives me just a sum which is incorrect as number of ids must be count unique.

Regards,

Michal

[Former Member · Accepted solution]

Hi Michael,

I think that the following formula will fulfill your requirement.

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

Regards,

Rogerio

[Former Member]

Hi,

I'm sorry but didn't understand your questionar.

Can you clarify it a little bit more?

Perhaps with a sample?

Regards,

Rogerio

[Former Member]

I might have explained it clearer - it's just to calculate count distinct ytd (like running aggregate).

Eg.

how to write a formula for 'no of unique IDs YTD'?

Month	no of unique IDs	no of unique IDs YTD
2015-01	34	34
2015-02	54	45
2015-03	21	76

Michal

[Former Member]

HI,

The number of unique IDs in each month you can get with Count([ID];Distinct) as you mentioned. I think that you will be abble to achieve the unique IDs YTD by using RunningCount([ID];Distinct)

Regards,

Rogerio

[Former Member]

Hi,

How about this formula :

Count ( [date])  in Block in ([date]).

Regards,

Onur

[Former Member]

hi,

close, but this returns total count for block (I mean the name number for every row).

Thanks anyway.

Regards,

Michal

[Former Member]

Does RunningCount support Distinct?

[Former Member]

=RunningCount([ID]) in ([ID]) will give you a result that is half way there when you include ID in your table - you'll see that the last value for each Year-Month is the number that you want. I'm struggling to figure out how to translate that into something without the ID in the table. Not sure if it's anywhere near a solution for you but it could be a start.