4 Replies Latest reply on Jul 6, 2017 2:23 PM by amulya.sharma

# Running Total of Count Distinct for entire dimension across Table

In the attached workbook, you'll see that I'm trying to count the distinct ID's over all preceding years. However, when I use the Running Total across Table calculation, it'll consider distinct values only in that year and add it to the immediately previous year to get the new total.

How do I count the total of these distinct ID's relative to all preceding years from that year? By this logic, 2015 should read 4, 2016 would read 7, 2017 would read 9 on the chart. Thanks.

• ###### 1. Re: Running Total of Count Distinct for entire dimension across Table

Hi Amulya,

One option would be to create a calculated field that records the first date that each ID appears. You can use:

{FIXED [ID] : min([Date])}

Then, if you replace [Date] with [Min Date] in your viz, it should get you what you're looking for.

Does this help?

Michael

1 of 1 people found this helpful
• ###### 2. Re: Running Total of Count Distinct for entire dimension across Table

See attached.

There is no WINDOW_countD(), which is what we really need here.  But I came across this hack where I can shove a 1 for every unique [whatever dimension you want to COUNTD] from the current mark backward to however far back you want to go.  Usually it's for a rolling countD over a given number of periods.  (So if you wanted a rolling 12-month count, then you would have -11 instead of FIRST() like I did in [period buffer].  -11 says to go back 11 periods, as well as the current period (indicated by the 0 in the 3rd argument in [period buffer].)  So FIRST() says to go all the way back to the beginning.

Now the [Running COUNTD] calc sums up all the 1s and gives you the count you want.

Yes, it's hack-y.

But glory be!  It works!

1 of 1 people found this helpful
• ###### 3. Re: Running Total of Count Distinct for entire dimension across Table

Michael.  That's nice.  I hadn't thought of approaching it that way.

Amulya -- See Sheet 3 in the attached.  That has Michael's solution.

• ###### 4. Re: Running Total of Count Distinct for entire dimension across Table

Yes. I have used LoD for similar situations in the past too but I am usually curious to see why I find myself needing to "FIX" so many dimensions every so often for a calculation that might seem pretty basic. Well, nothing's perfect and as long as it works, I'm game.