Moving Month Avg in pivot table

vidhya91gct — Tue, 04 Sep 2018 09:59:47 GMT

Hi All,

I am facing Issue in moving month Average as below,

Table :

I have stored the data as month wise , so currently fetched Aug'18 and Sep'18 and fiels name as Yearmonth

Year Month

Aug 2018

Sep 2018

i am using the colums in pivot table as below,

GYR- Green,Yellow and Red

Red Projects in Month - count(No of Red Projects in GYR) - count({$<[GYR ]={'R'}>}DISTINCT [Project ID])

To No of Projects - Count(Total Projects) - Count(Distinct Project ID)

Red Month - Red Projects in Month / Total no of Projects

I am facing issue on Avg and want to know, how to calculate Moving Month Avg(Logic as given below)

% Avg Red Projects - starting month Aug'2018 is 57.00%

Next month(Sep'2018) - (Red month for Aug'18 +Red month for Sep'18)/2

For Oct Month - (Red month for Aug'18 + Red month for Sep'18 + Red Month for Oct'2018 )/3

Please Help me to get moving month Average.

Thanks & regards,

Vidhya K

Re: Moving Month Avg in pivot table

Sergey_Shuklin — Tue, 04 Sep 2018 12:25:21 GMT

Hello!

You need to use RangeAvg(Above(*, 0, RowNo()) construction. PFA.

vidhya91gct — Tue, 04 Sep 2018 12:56:01 GMT

Thanks for your reply.

I tried but is not working in pivot table.

If select month field , reflecting fine , but overall it is not working ,because I am using yearmonth in dimension on pivot table.