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

# Distribute Measure Value over 12 Months

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

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

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

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.