2 Replies Latest reply on Jun 9, 2016 2:04 PM by d Vermaak

    Need help on creating a calculated field for projected values.

    d Vermaak

      Hi, I need help building a calculation for projected annual values.

      Year
      Claims
      2013100
      2015

      150

      2016200

       

      To do this in excel, I was averageing the # of monthly claims  and then adding that number onto 2016 for however many months are remaining. For instance, if there is an average of 20 claims per month and there are 5 months remaining, I would add 100 (20*5) onto 200 (2016's claims to date). 300 would now be my projected number.

      How would I do something like this in tableau? I'm unable to use the forecasting feature because it says the time series is too short. Is there a way to manually calculate a forecast?

        • 1. Re: Need help on creating a calculated field for projected values.
          Tina Hauser

          Hi Darren,

           

          A lot of questions here in order to really answer this, but I'll lay out a skeleton for one solution.

          Will you have monthly data in your dataset? Will you calculate the average in Tableau or will that be provided? Without monthly data we can't calculate the number of months remaining, either.

           

          To make an example work, I have set the # of months remaining to be 5, using a parameter.

           

          For the average, I've done an example of a calculated field using level of detail, and I've also used a parameter (depending on what your data will actually look like.)

           

          There are 2 calculated fields for the projected number, one using each method for average.

           

          If you have a data set or sample workbook to attach, I'm happy to help further, otherwise hope this gets you on the right track.

          Tina

          • 2. Re: Need help on creating a calculated field for projected values.
            d Vermaak

            Hi Tina,

            Yes, I have monthly data. I will need to calculate the avg.

             

            Data is as follows:

            Month of Date Administrator NotifiedQuarter of Date Administrator NotifiedYear of Date Administrator NotifiedCount of Claim Number
            JuneQ4FY 20164
            FebruaryQ3FY 20117
            AprilQ3FY 20119
            MayQ4FY 201113
            NovemberQ2FY 201217
            DecemberQ2FY 201118
            JanuaryQ2FY 201120
            SeptemberQ1FY 201223
            NovemberQ2FY 201125
            MarchQ3FY 201125
            OctoberQ1FY 201226
            JanuaryQ2FY 201226
            SeptemberQ1FY 201127
            NovemberQ2FY 201328
            FebruaryQ3FY 201228
            MayQ4FY 201330
            JulyQ4FY 201130
            AugustQ1FY 201131
            OctoberQ1FY 201131
            SeptemberQ1FY 201334
            DecemberQ2FY 201234
            MayQ4FY 201234
            AugustQ1FY 201235
            OctoberQ1FY 201337
            FebruaryQ3FY 201337
            MarchQ3FY 201237
            AugustQ1FY 201338
            JulyQ4FY 201339
            JuneQ4FY 201140
            JanuaryQ2FY 201341
            MarchQ3FY 201341
            AprilQ3FY 201241
            NovemberQ2FY 201442
            JuneQ4FY 201242
            DecemberQ2FY 201344
            FebruaryQ3FY 201444
            AprilQ3FY 201444
            SeptemberQ1FY 201545
            AugustQ1FY 201446
            SeptemberQ1FY 201446
            JanuaryQ2FY 201546
            OctoberQ1FY 201447
            JulyQ4FY 201248
            DecemberQ2FY 201449
            OctoberQ1FY 201550
            NovemberQ2FY 201551
            JanuaryQ2FY 201451
            FebruaryQ3FY 201552
            JuneQ4FY 201352
            JuneQ4FY 201453
            AprilQ3FY 201355
            AugustQ1FY 201558
            DecemberQ2FY 201558
            JanuaryQ2FY 201659
            MarchQ3FY 201459
            JulyQ4FY 201459
            MayQ4FY 201460
            MayQ4FY 201560
            AugustQ1FY 201663
            DecemberQ2FY 201664
            OctoberQ1FY 201670
            NovemberQ2FY 201670
            AprilQ3FY 201671
            FebruaryQ3FY 201674
            SeptemberQ1FY 201675
            AprilQ3FY 201575
            JulyQ4FY 201575
            MarchQ3FY 201583
            JuneQ4FY 201589
            MayQ4FY 201695
            MarchQ3FY 201698

             

            The problem is this needs to be dynamic. I need to factor in other variables such as region, state, districts, etc.