4 Replies Latest reply on Feb 8, 2016 7:20 PM by Nakarest Chumsuntivut

# Running total(Accumulate) distinct number by day

Hi Guys

I know this problem might be ask many times in this forum, I try to search about this problem and found some similar thread but still cannot solve my problem.
e.g. Running Distinct Sum or Total using COUNTD this should be most similar issue in my opinion.

So normally I can use COUNTD(Customer ID) to calculate amount of distinct customer but this can be only distinct customer of each day not all period.

As you can see in a picture total unique of customer is 1,130 but when I use table calculation to do a running total the number on last day shown as 1,480(it should be shown as 1,130).
How can I do a running total without duplicate value from previous days.

Thank you in advanced • ###### 1. Re: Running total(Accumulate) distinct number by day

Hi,

The problem you are facing is exactly why that (very long) thread exists. You're wanting the running COUNTD() to recompute the COUNTD() for every day whereas what it's doing is summing up the COUNTD(). You'll need to use one of the solutions from that thread.

Jonathan

• ###### 2. Re: Running total(Accumulate) distinct number by day

If you are using v9, then you can use LOD

{fixed Customer=min(orderDate)}

to calculate countd use:

countd(if OrderDate=FirstBuyDate then Customer end)

1 of 1 people found this helpful
• ###### 3. Re: Running total(Accumulate) distinct number by day

Hi Nakarest,

To summarize what my fellow community members

Jonathan Drummey and Nasrin Ahmed replied,

I would suggest using LOD calcs (hence version 9.0+).

Of course, one couldn't avoid RUNNING_ Table calculation,

but it is only the last calc to get the final result.

If one want to filter before applying FIXED LOD calcs,

then one should place filter(s) in Context. Essentially,

it does fix a scope for LOD and all subsequent calculations.

If one want to further narrow the view (hide some marks from it),

one should apply a Table Calculation filter, which does exactly that.

Please find the attached sample wb ( version 9.0).

Hope it helps to "Accumulate" :-)

Yours,

Yuri

1 of 1 people found this helpful
• ###### 4. Re: Running total(Accumulate) distinct number by day

Thank you for your answer LOD is working great and the result is exactly what I want  : )

At first I have a bit problem with some filter but after I look into a comment from Yuriy Fal and you workbook I can solved it and more understand in this formula.
Thank you again for you help guys.