Jump to content
We've recently updated our Privacy Statement, available here. ×

How can moving average include complete 13 weeks in serious


JOHN-jnpo
Go to solution Solved by Olivier Keugue Tadaa,

Recommended Posts

Hello,

I got a problem is 13 weeks moving average only the last week is correct with complete 13 weeks data.

the others week is not complete 13 weeks while I using OVER (LastPeriods(13,[Axis.X])) and visualization has 13 weeks in axis X.

What I need is visualize a 13 weeks moving average and every weeks are calculated with complete 13 weeks even the previously week are not show in visualization.

Example as below, only wk2416 has calculated by 13 weeks, and wk2404 only has calculated by we2404.

May someone help to resolve this kind problem? Thank you in advance.

 

image.thumb.png.eebd189f948178c5226b1b26ba62a279.png

Link to comment
Share on other sites

Hi John,

Trying to understand the problem.
Your weeks from 2404-2415 only have the individual values, whereas the week 2416 holds all the 13 weeks. Is that correct?

If so, you want to use a formula to calculate the moving average of 13 weeks, which typically can be done via this formula:
Sum([ORDER_AMOUNT]) THEN Avg([Value]) OVER (LastPeriods(13,[Axis.X])) THEN If(Count() OVER (LastPeriods(13,[Axis.X]))=13,[Value],null).

But if the assumption is true, then you need to replace the 2416 value first with it's real value before applying a moving average.

Kind regards,

David

Link to comment
Share on other sites

  • 2 weeks later...
  • Solution

Hi John, I'd like to propose a different approach based on calculated columns. Indeed, when you use a custom expression it only applies to the filtered data on the visualization. Therefore, your first 12 periods will always show wrong values for moving averages when you filter your data.

Hence,


First, add the following columns 
YearWeek :::: YearAndWeek([week])
moving avg::::Sum([value]) over (LastPeriods(13,[YearWeek]))

Then, use the following Y-Axis custom expression ::::  Sum([moving avg])

you will have this
image.thumb.png.dd8dafb15d0df1eaa5e4c92489bfe90c.png

 

instead of that 

image.thumb.png.e4572e159146304c771dcf4c211bd920.png

Note that both visualizations share the same filters 

image.png.f4b9cf0197b412b4cf90f16d6f170890.png

don't forget to set the X-Axis as categorical like this...

image.thumb.png.0fb0495631668bb4782e6afe663e4f8e.png

Let me know if this helps please (I've attached my data source)

moving avg.xlsx

Edited by Olivier Keugue Tadaa
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...