
1. Re: Fixed Formula
Mavis Liu Oct 12, 2018 3:12 AM (in response to Alex Martino)1 of 1 people found this helpfulHi Alex,
When you use AVG({ FIXED [Category], [Date]: SUM([Sales Volume])}) it will fix it against the dimension of category and the whole date itself. So this date is taking into account day, month and year and possibly the time as well if it's a date time field.
So the above is finding the sum of the sales volume for every single category and date, then finding the average. (I am using UK format dates)
e,g,
Category Date Sales Volume
A 01/03/2017 1
A 02/03/2017 2
B 01/04/2017 3
B 05/04/2017 6
If you do AVG({ FIXED [Category], [Date]: SUM([Sales Volume])}) the result will be 1+2+3+6 divided by 4, which is 3.
If you do AVG({ FIXED [Category], Month[Date]: SUM([Sales Volume])})
It will actually be:
Category Date Sales Volume
A March 3
B April 9
Which comes out to be 12/2 = 6
Using Month(date) just means it's finding the datepart of the date.
Thanks,
Mavis

2. Re: Fixed Formula
Alex Martino Oct 12, 2018 9:35 PM (in response to Alex Martino)But if you are fixing it to category, won't it just return the value at the category level, not the overall level?
For example, March for Product A will be 3 divided by 30 days. April for Product B will be 9 divided by 90 days.
This is what I see in my spreadsheet.

3. Re: Fixed Formula
Mavis Liu Oct 15, 2018 12:50 PM (in response to Alex Martino)Hi Alex,
If you fix it to category level in the calculated field, so if I do a AVG({ FIXED [Category]: SUM([Sales Volume])}) then the results will be:
Category Date Sales Volume FIXED
A 01/03/2017 1 3
A 02/03/2017 2 3
B 01/04/2017 3 9
B 05/04/2017 6 9
in other words :
Category Fixed
A 3
B 9
As what it's doing is summing it up per category, then finding the average. As category is in the view, finding the min/max/avg will give the same results.
Thanks,
Mavis