8 Replies Latest reply on Jun 26, 2018 9:49 AM by george dudley

# Apply 4-month Average Full Year of Monthly Data

Hello,

I have been tasked with creating a simple line graph comparing Sales to Budget.  The problem is that we don't have actual budget data, and the powers that be want to use the average of the first four months of the year and apply that as a constant monthly value for the full year.

I need to create a calculated field that will create that constant value.  Our data has month-year values, so I wrote a calc called Month Integer that pulls the Datepart (month) to use in the budget calculation.  Then I tried calculating the budget by using "If [Month Integer]>0 AND [Month Integer] <5 Then ([Sales]/4) END."  There are two reasons that doesn't work.  One is that it only graphs for Jan-April, and the other is that it doesn't create a constant value.  Basically I need to know how to make this appear as a reference line based on the Jan-Apr average that will adjust when filtered for Client, Region, etc.

I have attached a sample workbook based on some simplified dummy data.

• ###### 1. Re: Apply 4-month Average Full Year of Monthly Data

Hi

is this what you want

Here is a formula

then plot as a dual axis chart and sync the axes

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Apply 4-month Average Full Year of Monthly Data

Sorry George that wasn't it

I think this is it by client - but I am not sure that is want

Did you want a single flat line that represented the average over all clients or did you want a lingle line by client?

this is the all client result

each client is

Jim

• ###### 3. Re: Apply 4-month Average Full Year of Monthly Data

Hi Jim,

I really appreciate the time and effort you are putting in on this.

I haven't had time to really dig into it yet, but flat line is what they want.  If we are looking at all clients we want a line that will reflect the total sales for the first four months divided by 4.  I think that is 950.  If we are looking only at Client A the line would be at 275.  If we filtered for North the line would be at 575.  For whatever dimension we are looking at they want the Jan-Apr total divided by 4 as the total for every month.

Again, thank you for looking at this.  It looks very promising.

George

• ###### 4. Re: Apply 4-month Average Full Year of Monthly Data

Hi George

I think I finally got it

here are the formulas

and it will return this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 5. Re: Apply 4-month Average Full Year of Monthly Data

BTW - it is key that you place client and region on the filter shelf and add them to Context

Jim

1 of 1 people found this helpful
• ###### 6. Re: Apply 4-month Average Full Year of Monthly Data

Okay, we are really making progress!  I have to admit that I am doing some of this by rote, and don't really understand what the heck I am doing (☺), but heck, it's working.  I have a major problem with the sample workbook, which is that I can't synchronize the axes, the option is grayed out.  But I edited the calcs and applied them to the actual data and it appears to be working perfectly.  I have not yet done any serious testing, though.  It is about 30 divisions and 150 clients, so I have some math to do.

Jim, thank you so much for all your help.

George

• ###### 7. Re: Apply 4-month Average Full Year of Monthly Data

that's interesting are you doing something with multiple data sources that you didn't share?

anyway - are you following the process - after dual axis then edit the axis

worst come to worse - you set a fixed range for the axis - may not be what you want but it could improve the viz

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 8. Re: Apply 4-month Average Full Year of Monthly Data

HI Jim,

I applied these principles to the actual data and, after the expected hiccups, it looks like things are working.  I still don't understand exactly how it is working, but I messed with it, added the extra filters I needed, etc.  and did a few manual checks against the filters and it looks good.

I have marked this as the correct answer.

Thank you!  George