Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hello!
You need to use RangeAvg(Above(*, 0, RowNo()) construction. PFA.
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.