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

    Calculating projection based on a range for a parameter

    Geoffroy Magnan

      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 ?

       

      Thanks in advance

       

      Geoffroy

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

          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
            Geoffroy Magnan

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

            Available Space

            Contract

            2013

            20142015201620172018

            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

            B15.000 m²2013
            B15.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