1 Reply Latest reply on Sep 11, 2014 3:29 AM by juliette.mccormack

    calculate the frequency from a date range

    Dimitris Charalampou

      Hi to all,

       

      This is my first post in the community! Of course i am kind of a newbie with Tableau.

       

      I have the following problem:

       

      I have 2 tables. The first contains the contracts, The table has the id of the contract,  the start date and the expiration date of the contract. The second table has only the claims that the customers made for their contracts. It has the id of the contract (the same as the first table) and the date of the occurence of the claim.

      So the first table may have 10.000 rows, one for each contract id and the second only 1.000 rows for the claims, because most of the contracts ended without a claim from the customer.

       

      I want to calculate the claim frequency. It is calculated as:

       

      Frequency =           Number of claims

                                 Number of active contracts

       

      for a specific date, let's say per month. So i want to find the active contracts  for each month (the contracts for which each corresponding month exists between the  the start date and the expiration date of the contracts table and use it to divide the number of claims for the same month from the claims table.

       

      Any help would be very appreciated.

       

       

      Thanks in advance!

        • 1. Re: calculate the frequency from a date range
          juliette.mccormack

          Hi Dimitris

           

          Welcome to the forums.  Your question is good and clear, except for one thing - no attached workbook.  Please see Posting a Perfect Question.  The problem is that I find it so much easier to follow what you're explaining if I can look at it in the workbook as well - and I'm sure many other helpers do to - and it helps others who are learning from the discussion.  If your data is sensitive, then create a mock up or try to replicate the situation using the SuperStore data.

           

          I would be happy to have a look at it (use the 'Use advanced editor' button to attach it).

           

          Best wishes

          Juliette