You were using the right function--WINDOW_AVG(SUM(x))... you just need to get the partitioning and compute along fields set correctly. With any table calculation, you have to define these, and it's still a point of confusion for me often times.
In your case, you needed to be able to access the Month value of the date field and the Day value of the date field so that you could partition by Month and compute along Day. So, in the attached workbook, you'll see that I just created a second field that gives the Month value (truncating off the day, so 1/17/2012 and 1/5/2012 become 1/1/2012).
Then, I used the window_sum function and set the table calculation to calculate using Report Date (the description then states that results are compute "along Day of Report Date for each Month of Month--in other words, average the day totals, get a new average for each month).
One more thing--the FIRST () function is also a table calculation, but not one you'd want to use in this case. Essentially FIRST() or LAST() allow you to pick a single row of the partition, but you don't actually want that here because you want to get the value for each item in the partition and aggregate them...
Anyways, hope this helps!
Sample Workbook.twbx.zip 64.7 KB
In short, what I've been trying to accomplish is: using a sales data, calculate the number of days where sales effectively happened in a sequential form, with start and end on each month.
- January: Sales on the third, sixth and twelfth days, would be considered as 1°,2° and 3° day respectively. Same for every month, ignoring the dates where no sales happened.
With those available, calculate the average sales for each of those sequential days.