5 Replies Latest reply on Aug 30, 2018 6:41 AM by Francesco Cometa

    Projected values after a specific date

    Francesco Cometa

      I have a data source with different columns, among which VARIABLE, DATE, VALUE columns.

      After a specific DATE there are no more VALUEs in the datasource, so I need a calculated field (=Projected Value) able to project VALUEs after that Specific Date. Please you can find an example of this datasource just below:

       

      VARIABLEDATEVALUE
      = Value after Specific Date
      Profit31/05/201833445653344565
      Profit30/06/201821321322132132
      Profit31/07/2018 (specific date, selected via parameter)43214344321434
      Profit31/08/20180 (missing)4321434
      Profit30/09/20180 (missing)4321434

       

       

       

       

      My idea is to ask tableau for picking a specific  VALUE corresponding to a specific DATE (which I can choose via parameter) and use it after that specific date.

      I can't use Forecasting function because I don't have enough data (furthermore I need more flexibility).

      Something like the following

      Value after Specific Date

      IF [Date]>[Specific Date]THEN [Value at Specific Date]

      ELSE [Value]

      END

       

      where  [Value at Specific Date]: IF [Date] = [Specific Date] THEN [Value] END

       

      Finally I would like to apply a parameter to the Value after Specific Date, allowing a percentage increase/decrease time by time (via parameter as well).

       

      Thank you so much in advance,

      f.

        • 1. Re: Projected values after a specific date
          Shinichiro Murakami

          Hi Francesso

           

          I am not sure this is what you exactly need or not but here is another example of solution.

           

           

           

          Thanks,

          Shin

          1 of 1 people found this helpful
          • 2. Re: Projected values after a specific date
            Francesco Cometa

            Dear Shin,

            thank you so much for your helpful hint.

            I would need a further refining: instead of "previous_value" I need a "value at a specific date", where the specific date is a parameter from a list of available dates in the past.

            Moreover I need a date projection: I mean, whenever value is not available because it is still not reported, i need to project the "value at a specific date", where the specific date is a parameter from a list of available dates in the past.

            I will try to be more explicit with an example, as follows:

                 I have values up to January 2018; I need to project values in dates after January 2018; my idea is to use the value available in January 2018 (or even before) as a starting point to project values in February 2018 and so on, until a specified end date;

                 Finally these projected values should be subject to percentage increase/decrease via parameter.

             

            I hope this example helps to explain.

             

            Thanks again,

            f.

            • 3. Re: Projected values after a specific date
              Shinichiro Murakami

              Not exactly sure, but something like this?

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Projected values after a specific date
                Francesco Cometa

                We are almost there

                Conceptually you are right; the problem is that I have a data-source with values until let's say June 2018; after this date I have zeros as values.

                Now I need a formula able to pick a value from any date until June 2018 and copy it from July 2018 to a future date I would like to choose.

                So I need two date parameter: the date from which I pick the value to be copied in the future and the date until I want copy this value.

                • 5. Re: Projected values after a specific date
                  Francesco Cometa

                  Dear Shin,

                  I updated the data-source including DATES with empty values. Now I have a table similar to the following:

                   

                     

                  1BCD
                  2DateProfitProfit 2
                  3Mar-17    12,123      12,123
                  4Apr-17    34,432      34,432
                  5May-17    34,432      34,432
                  6Jun-17    12,332      12,332
                  7Jul-17    34,342      34,342
                  8Aug-17    65,656      65,656
                  9Sep-17             -        37,443
                  10Oct-17             -        45,814

                   

                   

                  "Profit" column is a calculated field, with aggregated values.

                  "Profit 2" column is another calculated field, which takes the corresponding "Profit" value when it is different of zero, otherwise the moving average of the previous three values of the column ""Profit 2".

                  Excel formula is like this (referring to yellow cell): =IF(C9<>0,C9,AVERAGE(D6:D8)).

                   

                  Do you know how to transpose this kind of formula in a calculated field in Tableau? The best should be to get moving average as parametric (i.e. deciding how many previous values to consider).

                   

                  Thank you so much!

                  f.