3 Replies Latest reply on Mar 16, 2017 9:23 AM by Jamieson Christian

    Distribute Measure Value over 12 Months

    Tyler Ricker

      All,

       

      Is it possible to evenly distribute a value over 12 months from the month of the effective date?

       

      i.e.

       

      $120,000 increase in price effective 4-1-2017

       

      Dimension

      Effective Date

       

      Measure:

      Actual Net Effect

       

      Distributes as so:

      April  - $10,000

      May - $10,000

      June - $10,00

      .

      .

      March - $10,000

       

       

      Effective DateActual Net Effect
      4/13/17$5,400
      4/3/17$38,800
      3/27/17$395
      4/1/17$120,000
        • 1. Re: Distribute Measure Value over 12 Months
          Jamieson Christian

          Tyler,

           

          Can you provide additional clarification? It's not obvious to me what question you're trying to answer. Consequently, the table you provide doesn't make any sense to me. (I can't even tell if it's supposed to represent your source data or the desired outcome.)

          • 2. Re: Distribute Measure Value over 12 Months
            Tyler Ricker

            It's an example of the two columns I'm referencing in my data source.

             

            If we, for example, have $120,000 of increased pricing effective 4-1-17, I want to distribute that out evenly out over a 12 month period. I don't want the entire $120,000 increase to only show in the effective month, but the data source only has it coded to the effective date, yet it will be taking place over the period of 12 months.

            • 3. Re: Distribute Measure Value over 12 Months
              Jamieson Christian

              Tyler,

               

              Okay, this will require some data prep. An ETL solution would be the easiest way to prep the data. If you need to do this in Tableau without the benefit of an ETL pipeline, your solution will roughly look like this:

               

              • A "scaffold" data source to establish the framework of years/months that you want to report on (because many of these values will likely not exist in your main data). (If you are unfamiliar with data scaffolding methods, here's a good place to start: scaffolding | Drawing with Numbers )
              • A union of the data onto itself to create 2 rows: one for the start of the period over which the price increase will be reported, and one the end of the period. (Depending on your data source, this may be doable with Custom SQL or with Tableau's Pivot feature.)
              • A set of table calculations used to trigger data densification and enable the injection of values into each month that a price change should be applied over. (This technique is described in these threads: Re: Distinct Head Counts Between 2 Dates without Running Totals (V.10)   and  Re: Graph to show the evolution of Delay Days (Service Orders) )

               

              The above is a high-level overview, intended largely to underscore that Tableau is not an ETL tool, and relying on it to shape your data will be more difficult than using a true ETL tool like Alteryx. You can do it, and you'll learn a lot about Tableau's guts in the process, but it can be rather inconvenient.