
1. Re: Sum by Day, Then Average by Month
Mark Holtz Nov 21, 2012 8:17 AM (in response to Aaron Rubinstein)Aaron,
You were using the right functionWINDOW_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 Monthin other words, average the day totals, get a new average for each month).
One more thingthe 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


2. Re: Sum by Day, Then Average by Month
Yannick Klausener Jul 13, 2016 11:10 AM (in response to Aaron Rubinstein)Aaron Rubinstein Any updates on this topic? I've been trying to do the same and Mark Holtz solution won't work as I need this calculation to be done in a row level.
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.
 Example:
 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.
Thanks
 Example: