5 Replies Latest reply on Oct 16, 2017 10:14 PM by .Kalin Stoimenov

# Rolling Count As of the End of Different Point In Time

Hello ,

I am trying to calculate the distinct number of records as of the end of each period (Parameter) after the beginning of the calendar year.

I didn't have that problem with the Rolling Amount (\$), but can't get to work the rolling count. Any ideas will be appreciated.

• ###### 1. Re: Rolling Count As of the End of Different Point In Time

Hi Kalin,

Find my approach as reference below and stored in attached workbook version 10.4 located in the original thread.

0. Year: year([Accident Date])

1. Months_From_Begin_Accident_Year: Datediff('month',[Accident Date],[Evaluation Date])

2. Records: ({fixed [Year],[Months_From_Begin_Accident_Year]:countd([Record ID])})

3. Amount > textmenu > Quick Table Calculation > Running Total

4. Drag required objects to the indicated locations.

Regards,

Norbert

• ###### 2. Re: Rolling Count As of the End of Different Point In Time

Hi Norbert,

Thanks for replying back, however I am afraid that his won't work .

this is what I expect to see - see the numbers in RED

and this is what you show

For example for year 2016 as of the end of January - I have 3 distinct records , as of the end of February for the same year -this  goes up to 5, as of end of March for the whole year - i see 5 , April - 6 and so on.. but for December that is the last month of the year - I see 8 .

Think about this as Loss Development Triangle (Insurance) https://www.captive.com/news/2017/02/20/basics-of-loss-development-triangles , where you see each record developed after certain point in time. However with the transaction data I don’t have transactions for each month – I calculate them as of the end of each month/quarter/ etc.

I hope this makes sense..

Thank you!

• ###### 3. Re: Rolling Count As of the End of Different Point In Time

Hi Kalin,

Could you explain the "definition" of your "7" so i can understand the required logic behind it?

Regards,

Norbert

• ###### 4. Re: Rolling Count As of the End of Different Point In Time

Hi Norbert,

It means that I have 7 Distinct records as of the end of that period , You basically have rolling count distinct for both periods combined.

So when it's 0 Months you will see 5 distinct records , when it's Month from Accident= 3 , then you will see two additional records so at  end of "Month 3" - I expect to see 7 (Running Count) .

The challenge is that I don't have transactions for each of the records for every month. So I believe i have to generate them somehow in tableau in order to get the correct running count.

I hope this makes sense.

Thank you!

• ###### 5. Re: Rolling Count As of the End of Different Point In Time

Hello,

I was able to resolve this calculation myself

The solution is simple ( took me some time) - for each record I have to pull only the first evaluation date and based on that I get the count of the rolling sum

You can see the final Version attached.

Thank you!