3 Replies Latest reply on May 7, 2018 12:22 PM by Okechukwu Ossai

    Calculate % Change of Contracts based on Parameter Start and End Date

    Brock Sibert

      Hi all,

       

      Unfortunately, I haven't been able to find a solution to this. I'm sure it's something quite easy that I'm just not thinking of. I have a dataset that shows the number of contracts for each individual in the department. Within the dataset, each contract has been assigned an as of date, so I am able to filter out contracts based on their date. However, I can't seem to figure out how to apply the date that the user has selected to my calculations.

       

      For example, say John Smith had 100 contracts open on 4/3/2018, 75 contracts open on 4/20/2018, and 50 contracts open on 5/3/2018, I would like to calculate the % difference based on the dates that were selected within the parameter. Ultimately, at this point, the user can select 4/3/2018, 4/20/18, and 5/3/2018 as a start date and the same for the end date (the number of available dates will rise as reports are pulled in the future). I can figure out the % change, but I'm not sure how to apply the parameter selections.

       

      I hope this makes sense. If not, I'm happy to provide any additional information.

        • 1. Re: Calculate % Change of Contracts based on Parameter Start and End Date
          Okechukwu Ossai

          Additional information is always helpful, especially a packaged dummy workbook with expected results.

           

          I don't know how you define an Open contract but I assume you may have a field called [Contract Status]. From your explanation it appears you want to calculate the number of open contracts between 2 different date ranges. If that is yes then it means you already have 4 different date parameters (Start Date1 to End Date1 and Start Date2 to End Date2). Without any other information about your data structure, I'll suggest a generic solution below. You'd need to adjust it to match your dataset.

           

          [First Selection]

          COUNTD(IF [Contract Date] >= [Start Date1] AND [Contract Date] <= [End Date1] AND [Contract Status] = 'Open' THEN [Contract ID] END)

           

          [Second Selection]

          COUNTD(IF [Contract Date] >= [Start Date2] AND [Contract Date] <= [End Date2] AND [Contract Status] = 'Open' THEN [Contract ID] END)

           

          [% Change]

          ([Second Selection]-[First Selection])/[First Selection]

           

          Hope this helps.

          Ossai

          • 2. Re: Calculate % Change of Contracts based on Parameter Start and End Date
            Brock Sibert

            Hi Ossai,

             

            I really appreciate your response. It is helpful, but not quite what I am looking for. I will try to provide more context. My data source looks like the following:

             

              

            Contract IDNameAs of
            1Company 14/20/2018
            2Company 25/3/2018
            3Company 35/3/2018

             

            If the contract is on the report it means it is open and needs to be closed. I have created some LOD calculations to tie the data into other sources that I have, including owners of the contracts. This has left me with the following on my Tableau sheet:

             

            Ultimately, I would like to create a column that shows the % change of the number of contracts based on what dates are selected in the start date parameter and the end date parameter. For example, for the above, it would be a column that shows the % change between the numbers in the column under May 3, 2018 (what is selected for the end date) and the column under April 3, 2018 (what is selected for the start date). Therefore, the new column would be 10%, 0%, 0%, -3.8%, etc and would change depending on the dates that are selected.

            • 3. Re: Calculate % Change of Contracts based on Parameter Start and End Date
              Okechukwu Ossai

              It will be good if you can attach a dummy workbook. I can't be of much help without seeing some dummy data.

               

              What field (dimension) do you have to the left of  April 3, 2018 column? What is the measure being displayed in the table? Do you already have working parameters and all you need is just a column displaying % change of open contracts between Start parameter and End parameter?