3 Replies Latest reply on Oct 6, 2014 4:56 AM by Dimitris Charalampou

calculate the frequency of  active records from a date range

Hi there,

I have a number of users in my table with the following fields:

The Id of the user, the account creation date and the account deletion date.

In another table i have the id of the customer and the date when he posted a comment.

I want to calculate the comment frequency, meaning how many users have made comments during the period that they were active, per month. For example if at January 2014 i had 2 users that made comments, and i have 5 users in my database but only 3 of them were active in January of 2014 then the frequency for that month is 66,67% (2/3).

Any suggestions?

Thank you.

• 1. Re: calculate the frequency of  active records from a date range

Dimitris,

Have you attempted using the count or distinct count? Add a sample set of the data so that a community "jedi" can mock up a solution.

Regards,

Tyler Garrett

Professional Services

Tableau Software

Visualize and Understand Tableau Functions

• 2. Re: calculate the frequency of  active records from a date range

Hi there,

Attached you will find an excel file with some test  data. In the first sheet i have the contracts table and the date range in which each contract is active. In the second sheet i have the claims table which shows all the claims for all of the contracts (Key= CONTRACT ID) and the event date of the claim. What i want to do is get the frequency ratio based on the event month of the claims.

So for example for February 2012, i have 4 claims that happened  (total claims, i don't care about distinct contracts) but only 2 distinct contracts where active at that month (Contracts with ID = 1 and 2). So the frequency for that month should be equal to: 4/2 = 200%.

For all of the months that there were no claims the frequency should by equal to 0. For September 2013 there was only one claim but there were 4 active contracts for that month.  So the frequency for that month should be equal to: 1/4 = 25%.

I hope i explained things enough.