3 Replies Latest reply on Oct 15, 2018 12:50 PM by Mavis Liu

Fixed Formula

How does a fixed formula work with date?

I have products and monthly dates and this formula to get the average daily amount by month

AVG({ FIXED [Category], [Date]: SUM([Sales Volume])})

When I do month[date] above it changes the numbers so that they are now sums of the month instead of the average daily amount. Why is this?

• 1. Re: Fixed Formula

Hi 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

1 of 1 people found this helpful
• 2. Re: Fixed Formula

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

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