7 Replies Latest reply on Dec 19, 2016 10:03 AM by Joao Gutheil

# How to sum rolling N months?

Hello,

I have a parameter that is the number of period (months). Ex: 3, 6, 12, 18...

How to sum column Sales rolling N months prior?

My dataset has 4 column:

CUSTOMER

NF NUMBER

DATE

SALES

Thank you

João

• ###### 1. Re: How to sum rolling N months?

Joao,

This would be easier to solve if you posted an example workbook, but here is the following thought process:

-Make sure the parameter is an Integer.

- you could use Window_Sum

- Do something like: WINDOW_SUM(sum([THING YOU WANT TO SUM), [Parameter 1], 0)

So Window_sum will now sum the thing you want to sum, going back X (X being the parameter selected) starting with the current location. You could also change the 0 using a parameter if you so choose.

I hope this helps and is detailed in a way in which you can re-apply to your case.

Best,

--Matt

1 of 1 people found this helpful
• ###### 2. Re: How to sum rolling N months?

Hello Mattew,

Thank you! I´m new to Tableau.

I would like to calculate rolling under N year prior.  Pls, take a look below. If i put pTempo = 3, i need to filter 2009,2010,2011. If i put pTempo = 2, i need to filter 2009, 2010 and so on.

• ###### 3. Re: How to sum rolling N months?

Joao,

It's my pleasure to help. For future reference, it's always best to upload a packaged workbook so we can give you the solution with your data. It's kind of a help us, help you thing

Example being I'm unsure of what pTempo is.

But in the meantime:

you could just create another parameter to do different calculations:

if Parameter = 1

then [DO THIS THING]

elseif parameter =2

then [DO THIS OTHER THING]

If I'm understanding your requirement, it would be

if [Parameter 2] = 1 then

WINDOW_SUM(sum([SUM OF THING]), 3, 0)

ELSEIF  [Parameter 2] = 2 then

WINDOW_SUM(sum([SUM OF THING]), 2, 1)

END

Unsure if that's exactly what you need. You will have to play with the numbers at the end.

Based on the previous post I removed the other parameter from the formula because it doesn't seem like you needed to roll N months. If you do, just add that back in.

Let me know the outcome and we can continue to work on this

• ###### 4. Re: How to sum rolling N months?

Hello Mattew,

Basically, i need to group data by customer and year, and sum sales.

But the year, i have to filter like pTempo, ex:Last 1 year, last 2 years... and so on,

Follow application thar i´m working with.

• ###### 5. Re: How to sum rolling N months?

Sorry Mattew,

How to attach the file?

• ###### 6. Re: How to sum rolling N months?

It's a little difficult to see:

• ###### 7. Re: How to sum rolling N months?

Mattew

Follow application that i´m working.

Thank you