需要为我的销售取过去 3 个月的窗口平均值 [英] Need to take window average of last 3 months for my sales

问题描述

我正在尝试使用window_avg"函数创建 3 个月的 wiondow 平均值计算.到目前为止每个月(从右侧查看)我都会得到正确的窗口平均值.但是，如果我使用订单日期从 11 月 1 日到截止日期进行筛选，那么我将无法获得 2015 年 12 月的正确平均值.

当过滤器中的订单日期为 11 月 1 日至截止日期时，我应该在 2015 年 12 月获得的平均数:(31045 + 75973 + 74920)/3 = 60766

当过滤器中的订单日期是 11 月 1 日到日期结束时，我在 2015 年 12 月得到的平均数:75446(这里不是 3 个月的窗口平均值，而是需要 2 个月的窗口平均值)

所以问题是:我如何确保即使我过滤了值，我也能得到 3 个月的正确窗口平均值，而不是基于过滤条件?

工作簿链接

这是另一种变体

Im trying to create a wiondow average calculation of 3 months using "window_avg" function. So far every month(viewing from the right) I get the correct window average. However If i filter down using the order date to 1st November to End of Date, then Im not able to get the correct average for December 2015.

What Average Im supposed to Get for Dec 2015 when Order date in filter is 1st Nov to End of Date: (31045 + 75973 + 74920)/3 = 60766

What Average Im getting for Dec 2015 when Order Date in Filter is 1st Nov to End of Date : 75446(Here instead of window average for 3 months it takes wondow average of 2 months)

So the question is : How can i make sure that even If I filter the values I get the correct window average of 3 months and not based on the filter criteria?

Workbook Link Here

I know that table calculations are based on what is in the view, but still is there a workaround for the same ?

解决方案

Set your filter to include all the data you want included in your table calc — that is, filter to include any prior months needed in data you want to display. So if December 2017 is the first month you want to display, and if your moving average requires 2 prior months, filter to start in Oct 2017.

Then right click on the headers and ‘hide’ the months you don’t want to display.

Table calcs are computed in Tableau based on the result set returned by the data source in response to Tableau’s query. Filters — other than table calc filters - control what data is included in the query result. Hiding marks just prevents them from being displayed.

The example below does effectively the same thing as described above, but uses parameters and a table calc filter to avoid having to manually hide marks. The calculated field [Within Date Range] is defined as [Order Date] >= DATEADD('month', -2, [Start Date]) and [Order Date] <= [End Date]

and [Start Date] and [End Date] are parameters.

Be sure to notice the filter based on the index () function. Table calc filters hide values, rather than exclude them from the results.

Here's another variation

这篇关于需要为我的销售取过去 3 个月的窗口平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！