5 Replies Latest reply on Nov 9, 2016 7:39 AM by Walt Reed

# help calculating monthly average

Hi Guys

This seems simple, but perhaps i am missing something.

I have a calculated field that works out a spend amount (in this case TLC 2)

I have calculated the TLC value from the previous month

I know want to compare previous month to the monthly average.

How do i create a calculated field giving me AVG(TLC 2) value by month? As can be seen in the screenshot i cant just do an  AVG.

the follow on from here is to then calculate the impact financially if scoring above or below average etc.

Any ideas?

• ###### 1. Re: help calculating monthly average

I am having a similar issue where I am trying to compare daily average to an average over past 3 years including today.

i am using the following formula to calculate the difference between two average and was leaving it upto to the filters i.e. date to execute the formula over right number of records.

AVG([Si Delay]) - AVG([3Y Past Delay Interim])

However, I do not think this is working. Any thoughts? Once the formula works I need to color code based on the result i.e. +ve or -ve.

• ###### 2. Re: help calculating monthly average

Are you going to show the months on the viz, or do you want to only calculate the monthly average?

• ###### 3. Re: help calculating monthly average

Hi Walt

Only calc the monthly average so i can use it with other calculated fields.

Al

• ###### 4. Re: help calculating monthly average

So this was a three-step process for me--there's probably more elegant ways to do this. I used the Sample - Superstore data.

First, I created a field to get the Year and Month of the Order Date:

Next, I did a FIXED LOD expression to get the sum of sales by Year Month:

Lastly, I did an EXCLUDE LOD expression to average the Sum of Monthly Sales:

In this last screenshot, you can see that the average monthly sales of \$47,858 is showing on each Label, so if you added different fields to the viz, the last calculation will remain the same.

Hope this helps..

Walt

• ###### 5. Re: help calculating monthly average

Or more elegantly, a nested LOD expression: