6 Replies Latest reply on Dec 12, 2012 7:36 PM by Eric Johnson

Calculate moving sum of values, year to date

Hello,

I am not really familiar with window calculations. i was wondering if windows calcs can be used to sum up values from a given date to a 365 days before? Can someone direct me to a good resouce where I can learn windows calculations better?

And what do you call the process of manipulating the data before it gets imported into tableau? if anyone has a resource on that it would be most helpful!

• 1. Re: Calculate moving sum of values, year to date

Hi Hub,

Window_Sum can be used to sum up 365 days in your data set if the days are displayed out and consistent enough. For any moving calculation, I generally find it easier simply using the "add table calculation" dialog, but if you are looking for resources, the manual is really good on this subject matter and there's a video on the table calcs as well.

Hopes this helps!

Wilson

1 of 1 people found this helpful
• 2. Re: Calculate moving sum of values, year to date

Ah boo. I don't think it works with data because of the multiple date entries, variable number of lines....

I'm trying to sum up all the units ordered from a particular date to a year back. (If order date is 9/1/12, then the sum of all the sales from 9/1/11 to 9/1/12). can you recommend a different approach than the window_Sum()?

I'm trying to use if statements but it isnt allowed because i cant mix aggregate and non aggregate statements.

But thank you for the resources sir!

• 3. Re: Calculate moving sum of values, year to date

Hi Hub,

Can you include a small sample table of what your data might look like?  If there's challenges around uneven dates, there might be some solutions by using different date roll ups and having the window_sum restart appropriately.  I suspect we can do it since Window_SUM were design specifically to handle moving sum calculations effectively.

Best,

Wilson

• 4. Re: Calculate moving sum of values, year to date

Hello Wilson,

Thanks for the continued forum exchanges. I added a packaged workbook... tried using regular calculations but

"If attr(Dateadd('year',-1, Order_Date)) < attr(Order_Date) then sum(Qty_Ordered) end"

doesn't work.  I understand why it doesnt work. I just don't have any ideas around it.

• 5. Re: Calculate moving sum of values, year to date

Oops sorry. I guess i cant use windows calculations. cant use windows calculations if there are no tables, right?

What i'm actually trying to do is graph the X-month period of sales over time. So for 8/1/12, that value would show the sum of sales from (X-months before order date) to the order date.

Does anyone have method to find the sum of values over a period of time? do i need an independed date period?

I need this to create an inventory turns dashboard, which is (sum of unit sales for 12 month period) / (sum inventory levels)

• 6. Re: Calculate moving sum of values, year to date

Is there still no solution to this issue? I need to chart monthly Trailing-12 values.

Thanks