2 Replies Latest reply on Aug 6, 2013 6:48 AM by Geoffroy Magnan

# Calculating projection based on a range for a parameter

I'm trying to "simulate" the available space of rented buildings based on their area and the end date of the contracts.

A basic example could be the following :

 Contract Area End date A 15.000 m² 30-05-15 B 25.000 m² 31-01-18 C 43.000 m² 31-03-16 D 10.000 m² 15-12-20 E 30.000 m² 30-06-17

I'd like to get a graph showing me the total available area in 2013, 2014, 2015, etc... based on the end of the contracts.

I can already get the info, but only year per yearn using this technique :

1. I created a parameter, called "Simulated year", which is used to select the year for the analysis

2. I compare the "End date" and the "Simulated Year", and then define a boolean Calculated field called "Availability"

3. I created a "Effective available area" which is the area of the rented space, multiplied by the "Availability"

Adding these in a table gives me, year per year, the effective available area, but the year of the analysis has to be selected manually, and I can't obtain a nice graph of the evolution of the available area for the coming years.

Is there a way to graph the "Effective available area" based on a range that I would define for the "Simulated Year" parameter, or is there another way to solve this question ?

Geoffroy

• ###### 1. Re: Calculating projection based on a range for a parameter

I believe you're missing a date dimension that runs from 2013 to 2020 to be able to achieve what you want to do.

The only workaround I can think of is to export the data obtained by each year - meaning you move the parameter and extract the data in a excel file for each year. Then you put all the data in a single file while adding a new column that indicates the value of the parameter for that particular data. (so for the extract you did when the parameter was in 2013, then you put 2013 under that column for the data obtained)

Then you reopen Tableau with the new data and you'll have your new data dimension to achieve the graph you want to display.

1 of 1 people found this helpful
• ###### 2. Re: Calculating projection based on a range for a parameter

This is what I thought... And a table formatted as following won't work either :

 Available Space Contract 2013 2014 2015 2016 2017 2018 A 15.000 m² 15.000 m² 15.000 m² B 25.000 m² 25.000 m² 25.000 m² 25.000 m² 25.000 m² 25.000 m² C 43.000 m² 43.000 m² 43.000 m² 43.000 m² D 10.000 m² 10.000 m² 10.000 m² 10.000 m² 10.000 m² 10.000 m² E 30.000 m² 30.000 m² 30.000 m² 30.000 m² 30.000 m²

Because tableau won't be able to use the date as a single dimension...

This means that I would have to transform this table to a much more complex one with a single row for each year contract and every year to analyze :

 Contract Space available Year A 15.000 m² 2013 A 15.000 m² 2014 A 15.000 m² 2015 A 0 m² 2016 A 0 m² 2017 B 15.000 m² 2013 B 15.000 m² 2013 ... ... ...

An so on for each contract and each projected year... As the main data must be adapted quite often (with new details for each contract), this seems not useable without a good ETL tool that would make such transformation automatic.

Thanks for the insight !

Geoffroy