6 Replies Latest reply on Dec 1, 2017 5:39 AM by Paul.Labuda

# Rolling last X months into last bucket and calculate it.

Hi Guys,

I'm trying to find out, whether it is possible to roll last X months from data set into last 'cell'.

We have a data set from last 365 days. Note that we have have more than 1 record per day.

What we would like to achieve is to have a parameter with the following integers: 2, 3, 6. Based on this parameter, we would like to roll last 2, 3, 6 into last month and calculate an average of all values.

Thanks!

• ###### 1. Re: Rolling last X months into last bucket and calculate it.

Hi,

Create a parameter to select rolling value.

Create a calculated field to get rolling average.

Solution workbook created in v10.3 is attached.

Hope this helps.

~Tushar

• ###### 2. Re: Rolling last X months into last bucket and calculate it.

Hello Tushar, That works just fine when we have full data, thank you. Unfortunately there is a case, when we can have a gap and no data for one of the months. In that case, this 'missing' month should be also taken into account while rolling. Is this possible?

• ###### 3. Re: Rolling last X months into last bucket and calculate it.

Hi Piotr,

Update the formula as below to handle the missing months.

Hope this helps.

~Tushar

• ###### 4. Re: Rolling last X months into last bucket and calculate it.

Hi Tushar,

I'm working on similar case in my company.

Unfortunately this isn't work. Look please, in my case (SUM) it should be.

MonthValueSUM(Value Rolled 3 Months)

January

1no have data for 3 months
February2no have data for 3 months
March36 (1 + 2 + 3)
April49 (2 + 3 + 4)
May512 (3 + 4 + 5)
June615 (4 + 5 + 6)
July718 (5 + 6 + 7)
August821 (6 + 7 + 8)
October1018 (8 + 0 + 10); 0 - missing value for September
December1222 (10 + 0 + 12); 0 - missing value for November

I'm working on similar case in my company
• ###### 5. Re: Rolling last X months into last bucket and calculate it.

Hi Piotr and Paul,

If you've missing values then it is very difficult to get the desired result, as Tableau will only work on the available data. So, to make it work you will have to create a separate table wherein you can add all possible date values. You can create this in your db,or in excel. Join this table with your existing tables using left or right join. With this in mind, I created one master that includes all the date values for this year.

Month:

Now, I connect to this file along with the actual file (Date_val) and performed cross-join.

Create a calculated field to get running sum/avg.

If you want, you can hide the missing values.

And the final result.

Let me know if this helps.