7 Replies Latest reply on Feb 13, 2018 4:32 AM by Anmol Parimoo

# 12 Rolling Total

Hi,

I am relatively new to Tableau and am trying to calculate the sum of prior 12month for, let's say, sales.

For example I have data from Jan15-Jan18 and for each month I want to calculate the total sales for the month + prior 11 months ( for example- against Jan18 total from Feb17 to Jan18, against Dec17 the total from Jan17 to Dec17). The final date is set separately as a parameter and will change monthly. I would need to use the calculated amount in other calculations in the future as well.

I tried a few things including the data filters, groupings but cannot get this to work. I included a sample workbook with the dummy data ( unedited).

Hopefully this is a way to achieve this?

Rox

• ###### 2. Re: 12 Rolling Total

Hi Roxana,

Will you try saving your workbook as a .twbx (File > Export packaged workbook) and reattach. Right now, we can't see your data in your workbook.

• ###### 3. Re: 12 Rolling Total

Thanks for quick responses- I've reattached in the right format

• ###### 4. Re: 12 Rolling Total

May be you are looking for this...See attached.

Thanks

Deepak

• ###### 5. Re: 12 Rolling Total

You should try and pivot the data first in the format:

Date               |               Sales

02/01/2015     |          1025.41

02/01/2016     |          3225.41

03/01/2015     |          4025.41

03/01/2016     |          9965.41

Then you might be able to apply the calculations.

• ###### 6. Re: 12 Rolling Total

Hi,

Thanks for looking into this - I really appreciate it.

Maybe I wasn't too clear in my original question- I am trying to find a way to calculate the sum of prior 12month for any month ( I've included below an extract showing the desired result- last 2 columns). In Excel this could be achieved by a very simple 'sum' formula.

I think your solution shows the total for the whole year so far ( i.e 2015/ 2016 part year) which is useful but not exactly what I was trying to achieve.

 Current Data Current Data Desired result-12m rolling total formula ( in excel) Date Pivot Field Names Sales 01/01/2015 100 02/01/2015 105 03/01/2015 110 04/01/2015 115 05/01/2015 120 06/01/2015 125 07/01/2015 130 08/01/2015 135 09/01/2015 140 10/01/2015 145 11/01/2015 150 12/01/2015 155 1530 =+SUM(B5:B16) 01/01/2016 160 1590 =+SUM(B6:B17) 02/01/2016 165 1650 =+SUM(B7:B18) 03/01/2016 170 1710 =+SUM(B8:B19) 04/01/2016 175 1770 =+SUM(B9:B20) 05/01/2016 180 1830 =+SUM(B10:B21)
• ###### 7. Re: 12 Rolling Total

Kindly use the below logic:

IF

DATEDIFF('month',[Order Date],

MAKEDATE([Year],[Month],1)

)<= 12

AND

DATEDIFF('month',[Order Date],

MAKEDATE([Year],[Month],1)

)>0

THEN 1 ELSE 0 END

Filter the sheet by value '1'.

You refer to the below workbook for any details.