Hi All,
I am facing Issue in moving month Average as below,
Table :
| Project ID | GYR |
| 1 | Y |
| 2 | R |
| 3 | R |
| 4 | G |
| 5 | R |
| 6 | Y |
| 7 | R |
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,
| Aug'2018 | Sep'2018 | Oct'2018 |
| % Avg Red Projects in Month | 57.00% | 49.95% | 42.82% |
| Red Projects in Month | 4 | 3 | 2 |
| Total No of Projects | 7 | 7 | 7 |
| Red Month | 57.00% | 42.90% | 28.57% |
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
