Quick measure of 12-month rolling average does not work?

[Anonymous]

Hi,

I used Quick Measure to calculate a 12-month rolling average of a monthly percentage. However, the result seems wrong as it's the same of the monthly percentage? I have 2 years of monthly data from Jan-2018. So in theory, for Jan-2019, the rolling average should be the average of 12 months in 2018. But it shows up the same as Jan-2019 percentage?

I can't figure out what I have done wrong. I used the default New Quick Measure window and didn't put in any code, only dragged and dropped various column labels into boxes. Please can you share your thoughts and ideas?

Thanks,

[Gordonlilj]

Hi,

The most likely solution is what values you put into Period, Periods before and Periods after.

Try something like i've done below and see what values you get

[Anonymous]

Thanks. I tried, using -11, or -12, the resulted average is still the same as the actual monthly average. 

[Gordonlilj]

Is it possible that you could provide some sample data and your expected result?

[Anonymous]

Hi, Gordon

This is a sample data table:

Chargeable%	Chargeable	Team	Time
85%	Y	TeamA	28/01/2018 00:00:00
79%	N	TeamB	28/01/2018 00:00:00
81%	N	TeamC	28/01/2018 00:00:00
90%	Y	TeamB	28/02/2018 00:00:00
78%	Y	TeamC	28/02/2018 00:00:00
86%	Y	TeamA	28/02/2018 00:00:00

The data goes from Jan-18 to Apr-19. What I am trying to calculate is the 12-month moving average of the chargeable% for each team. The time column uses only 28th of every month because it's a function generated from the actual Year column and Month column (in format of 1,2,3....12). I am beginning to wonder if it's the problem of this time column so the time function doesn't work. What do you think?

Many thanks

[Gordonlilj]

Hmm,

I'm not able to replicate the issue using the sample data in any way and creating it as you normally would

i get the correct rolling average.

So at this point i'm not entirely sure where the issue is coming from.

It might be something with your data that causes it. How are you visualizing the measure?

[Anonymous]

Hi, Gordon

Thanks. I am pretty sure that it's some of the month/time column filter that has gone wrong. I tried to stripped the data down to the basic without all the messy stuff built up in my trial-and-error, and then the moving average works perfectly.

So nothing is wrong with the quick measure, just too much convoluted mess in the building up of the file. I guess the easiest way is to simply rebuild the file as I now know what I was looking for.

Thanks a lot for your patience and your time. Much appreciated.