6 Replies Latest reply on Jun 22, 2018 5:07 AM by Pak Hang Leung

# How to use the calculation field to compute the average data for past couple months

Hi all

In my department, there is a own prediction model, and I have to visualize it through tableau.

The tableau table is similar as follows:

DateSales
Jan-1710000
Feb-1712000
Mar-1715000
Apr-1713500
.....
Jan-1818500
Feb-1819500
Mar-1820500
Apr-1822500

So now I have to make the forecasting for May-18.

The formal that we use is the growth rate is: (the average sales for past 3 months - the average sales for past 3 months in same period in last year) + The sales for the certain month in last year.

The first step that I want to do is, to look up the figure for the past 4 months.

I tried to use the following function to extract the figure last months by setting new variables and the time parameter

IF DATETRUNC('month', [Visit month]) = DATETRUNC('month', DATEADD('month', -1, TODAY()))

THEN [total billings] END

And turn out I got sth like this

DateTotal salesSales monthSales last 2 month
Feb-181950019500
Mar-182050020500
Apr-1822500

And if I want to but a calculated field and return the figure i.e the average for the past 4 months and the average for the past 4 months in last year, how should I do that?

Or any better solution that I can use to solve the problem?

Many thanks for your help in advance!

• ###### 1. Re: How to use the calculation field to compute the average data for past couple months

Hi Pak,

Maybe try something like this for the previous 3 months average:

avg(if

DATETRUNC('month', DATEADD('month', 4,[DATE])) > DATETRUNC('month',{ FIXED : MAX([Month])})

and DATETRUNC('month',[DATE]) < DATETRUNC('month', { FIXED : MAX([Month])})

= true

then ([VALUE)

END)

For the previous year's 3 months:

avg(if

DATETRUNC('month', DATEADD('year',1, DATEADD('month', 4,[Month]))) > DATETRUNC('month',{ FIXED : MAX([Month])})

and DATETRUNC('month', DATEADD('year',1,[Month])) < DATETRUNC('month', { FIXED : MAX([Month])})

= true

END)

Thanks,

Mavis

• ###### 2. Re: How to use the calculation field to compute the average data for past couple months

Hi Mavis , first thanks for your prompt help! But do not know why the figure is not accurate .... And can you explain more about the function =) ? Because I am nearly totally new in this area and would like to know more how the problem can be solved. Thanks again!

• ###### 3. Re: How to use the calculation field to compute the average data for past couple months

Hi Pak,

Have you got the dataset which you can attach? Then if you could let me know what your expected result is that would be great.

Note: Date truncate truncates the date down to the date part specified. E.g. 05/06/2018 using a date trunc for month becomes 01/06/2018.

So for the previous 3 month's average:

avg(if

DATETRUNC('month', DATEADD('month', 4,[DATE])) > DATETRUNC('month',{ FIXED : MAX([Month])})

and DATETRUNC('month',[DATE]) < DATETRUNC('month', { FIXED : MAX([Month])})

= true

then ([VALUE)

END)

The month-year of the date has an extra 4 number of months added to it. When this is greater than the fixed max month-year, then it will be set as true. The fixed calculation is an LOD calculation which ignores all filters and only looks at the very maximum date in the WHOLE dataset.

Say the max date in the dataset is June 2018:

If we add 4 months to March, this becomes 'July',  April, this becomes 'August', May becomes 'September' and June becomes 'October'. All these months are greater than June 2018 so are valid. However, then it becomes a bit endless because July can then become November, August becomes December etc... and these are greater than June.

If you want to exclude the month of June in the view when you're looking at the previous 3 months only, then that's what the line: and DATETRUNC('month',[DATE]) < DATETRUNC('month', { FIXED : MAX([Month])})  is for.

Thanks,

Mavis

• ###### 4. Re: How to use the calculation field to compute the average data for past couple months

Hi Mavis

That's awesome! I tried a bit further by googling and tried to summarize the data in the table, and the workbook is attached as your reference.

I tried to use the figure i.e (Avg sales in 3 months - Avg.sales in 3 months in last year) + Same month in last year in the variable Prediction figure , there is no figure returned in the table.

If I want to come up with the figure, and put it in a  new date record e.g June 2018, July 2018, Aug 2018 etc as a kind of forecast, how can I make it?

Again, thanks again and you are simply great =)!

Have a nice day,

Pak Hang

• ###### 5. Re: How to use the calculation field to compute the average data for past couple months

Hi Pak,

Apart from the already built in forecasting within Tableau, this is going to be tricky as you want it calculated in a really specific way, I'd suggest using multiple table calculations to do this.

Please see the attached, so to predict June 2018, this is what it works out:

Current Year 3 Previous 3 months average: 676,667

Current Year 3 Previous 3 months average: 423,333

Difference between the two is 253,333

Same month last year: 400,000

So the forecast would be 653,333

Is that right? Please see attached.

To be honest, Tableau isn't really the tool to do the forecasting you want.

Thanks

Mavis

• ###### 6. Re: How to use the calculation field to compute the average data for past couple months

Hi Mavis

Yes, that's exactly what I need =)

Just one more question: When I tried to extend the prediction by changing the date prediction calculation,

It returns the same number of rows e.g February 2017 to June 2018, or July 2018 to November 2018.

If I want to have like 6 months in advance i.e with the same algorithm and show the data from January 2017 to December 2018, how should I make the adjustment?

P.S I also find out the problem that you mentioned.....but still, many thanks and it's a great alternative =)

Pak Hang