tableau api - Compute rolling average across years while displaying data split by year

Question

The dashboard in the linked workbook shows a table with sales split by year on the top. Below, there's a table with the rolling average of the last 4 weeks, including the current. It's set to show NULL if there are not enough data points. I'd like for it to compute the first January 2018 value based on the current week and 3 full weeks from the end of 2017. Carrying that concept forward, all NULLs from 2018 onward will be eliminated. The NULLs for the first 5 weeks of 2017 will be the only NULL values. The average should always be computed on a full 4 weeks (28 days) even when week 53 doesn't contain 7 days.

How can I write a calculation to achieve what's described above?

I've tried putting the WINDOW_AVG function inside a LOD, but that's not allowed. Furthermore, I've also tried using FIXED and even FIXED inside WINDOW_AVG.

Here's one of my attempts:

{FIXED  [Week_int]:
WINDOW_AVG(SUM([Sales]), -4, 0)
}

It returns this error: "Error: Level of detail expressions cannot contain table calculations or the ATTR function"

Here's the data structure. It includes one value of Sales per day.

question from:https://stackoverflow.com/questions/65646115/compute-rolling-average-across-years-while-displaying-data-split-by-year

Answer 1

Basically I created a dummy data in Excel by creating dates (from 1-1-2017 to 2-2-2021) and filling some random values (unif dist *5000) against these.

I added Week[date] to columns and year[date] to rows as in your screenshot. I added sum(value) on the text marks card.

Thereafter, I added table calculation --> Moving average --> edited it for previous 4 values , next 0 values, (check current value if you want to include current record), then check Null if there are not enough values. (your requirement). --> click compute using -Specific Dimensions change the order of fields below - drag Year above than week (table across then down will also create the same view)

You should be able to get a view as desired.

Regarding your query on number of days in the week, Tableau caters it automatically if you have chosen it datepart.

Edit I verified this in Excel, the method is correctly working.

See, the average of first 28 values in Excel

and the view built in tableau:

Here's the corrected dashboard hosted on Tableau Public.