
1. Re: How to use the calculation field to compute the average data for past couple months
Mavis Liu Jun 21, 2018 8:18 AM (in response to Pak Hang Leung)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
then ([Gross Adds])
END)
Thanks,
Mavis

2. Re: How to use the calculation field to compute the average data for past couple months
Pak Hang Leung Jun 21, 2018 8:43 AM (in response to Mavis Liu)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
Mavis Liu Jun 21, 2018 11:45 PM (in response to Pak Hang Leung)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 monthyear of the date has an extra 4 number of months added to it. When this is greater than the fixed max monthyear, 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
Pak Hang Leung Jun 22, 2018 1:38 AM (in response to Mavis Liu)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

Sales prediction.twbx 61.5 KB


5. Re: How to use the calculation field to compute the average data for past couple months
Mavis Liu Jun 22, 2018 2:29 AM (in response to Pak Hang Leung)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

Sales prediction_v10.2.twbx 122.6 KB


6. Re: How to use the calculation field to compute the average data for past couple months
Pak Hang Leung Jun 22, 2018 5:07 AM (in response to Mavis Liu)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