1 of 1 people found this helpful
Also, if the Goal is do an average (where we set the value of missing data to Zero) then adding ZN() to your Measure might resole the issue.
This way the NULL Values gets populated with Zero (Once you turn on "Show Missing Values").
Thanks for the reply, so far no luck.
Here is what Tableau is doing - when I ask Tableau to take Window Average of Sum of the last 4 periods - using Moving Average function the one with -3, including current record, Tableau ignores Week buckets where there are no data. This happens even if I put zn or ifnull etc.
If you look at the table I gave in original post, that is the behavior demonstrated by Tableau - that is after using Zn as well as showing missing values. I even tried to bring in all dates using left join, but moment I ask Tableau to do Moving Calc with Window Avg, it just ignores all week buckets where there is no data, and only picks up columns or date bucket where there are data.
Ok, let me take a step back.
So your source data looks something like this
Where you have missing Dates between each week. Before we do anything special, it looks like this in Tableau.
When you "Show Missing Values" the table looks like this
And you want to do a WINDOW_AVG(SUM([Data], -3, 0), with the Missing Dates being padded out, where missing weeks is getting a value of Zero?
Or do you want it to simply do the Avg of the last 4 periods and Null Values remain NULL?
I added "Actual Data Points to Avg" so that you can see what the WINDOW_SUM in Each Partition is getting Divided by.
When Null values are left NULL
When NULL Values are counted as Zero
I'm trying to figure out where you are at, and what problems you are facing, that is not allowing you to do this.