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

# Need help on creating a calculated field for projected values.

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.

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.

Hi Tina,

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

Data is as follows:

 Month of Date Administrator Notified Quarter of Date Administrator Notified Year of Date Administrator Notified Count of Claim Number June Q4 FY 2016 4 February Q3 FY 2011 7 April Q3 FY 2011 9 May Q4 FY 2011 13 November Q2 FY 2012 17 December Q2 FY 2011 18 January Q2 FY 2011 20 September Q1 FY 2012 23 November Q2 FY 2011 25 March Q3 FY 2011 25 October Q1 FY 2012 26 January Q2 FY 2012 26 September Q1 FY 2011 27 November Q2 FY 2013 28 February Q3 FY 2012 28 May Q4 FY 2013 30 July Q4 FY 2011 30 August Q1 FY 2011 31 October Q1 FY 2011 31 September Q1 FY 2013 34 December Q2 FY 2012 34 May Q4 FY 2012 34 August Q1 FY 2012 35 October Q1 FY 2013 37 February Q3 FY 2013 37 March Q3 FY 2012 37 August Q1 FY 2013 38 July Q4 FY 2013 39 June Q4 FY 2011 40 January Q2 FY 2013 41 March Q3 FY 2013 41 April Q3 FY 2012 41 November Q2 FY 2014 42 June Q4 FY 2012 42 December Q2 FY 2013 44 February Q3 FY 2014 44 April Q3 FY 2014 44 September Q1 FY 2015 45 August Q1 FY 2014 46 September Q1 FY 2014 46 January Q2 FY 2015 46 October Q1 FY 2014 47 July Q4 FY 2012 48 December Q2 FY 2014 49 October Q1 FY 2015 50 November Q2 FY 2015 51 January Q2 FY 2014 51 February Q3 FY 2015 52 June Q4 FY 2013 52 June Q4 FY 2014 53 April Q3 FY 2013 55 August Q1 FY 2015 58 December Q2 FY 2015 58 January Q2 FY 2016 59 March Q3 FY 2014 59 July Q4 FY 2014 59 May Q4 FY 2014 60 May Q4 FY 2015 60 August Q1 FY 2016 63 December Q2 FY 2016 64 October Q1 FY 2016 70 November Q2 FY 2016 70 April Q3 FY 2016 71 February Q3 FY 2016 74 September Q1 FY 2016 75 April Q3 FY 2015 75 July Q4 FY 2015 75 March Q3 FY 2015 83 June Q4 FY 2015 89 May Q4 FY 2016 95 March Q3 FY 2016 98

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