Why window_avg in tableau has to work on aggregated variable?

Question

I am trying to calculate a rolling average by 30 days. However, in Tableau, I have to use window_avg(avg(variable), - 30, 0). It means that it is actually calculating the average of daily average. It first calculates the average value per day, then average the values for past 30 days. I am wondering whether there is a function in Tableau that can calculate directly rolling average, like pandas.rolling?

Answer 1

• In this specific case, you can use the following

window_sum(sum(variable), -30, 0) / window_sum(sum(1), -30, 0)

• A few concepts about table calcs to keep in mind

  • Table calcs operate on aggregate query results.

  • This gives you flexibility - you can partition the table of query results in many ways, access multiple values in the result set, order the query results to impact your calculations, nest table calcs in different ways.

  • If you can calculate what you need simply from the aggregate results that you've already fetched, this approach can also give you efficiency 

  • It also gives you complexity. You have to be aware of how each calculation specifies the addressing and partitioning of the query results. You also have to think about how double aggregation will impact your results.

  • In most cases, applying back to back aggregation functions requires some careful thought about what the results will mean. As you've noted, averages of averages may not mean what people think they mean. Others, may be quite reasonable, say averages of daily sales totals.

  • In some cases, double aggregation can be used without extra thought as the results are the same regardless. Sums of Sums, Mins of Mins, Max of Max yield the same result as calling Sum, min or max on the underlying data rows. These functions are called additive aggregation functions and obey the associative rule you learned in grade school. Hence, the formula at the start of this answer.