Hello , my requirement count no of people in the grp , in given period of time . the Catch is I have to use recursion function for this pivot report. E.g let take 11115 person ID ,
| 11115 | February | 2016 | D |
| 11115 | March | 2016 | D |
| 11115 | April | 2016 | B |
| 11115 | May | 2016 | B |
| 11115 | June | 2016 | C |
| 11115 | July | 2016 | D |
As you see , this person moved 3 times from one grp to another. So in our requirement we have to compare persons last plan grp with all the previous group and add him against that grp if the plan group is different .
| | | Recent Plan GRP |
| Prev Plan Grp | | A | B | C | D |
| | A | | | | |
| | B | | | | 1 |
| | C | | | | 1 |
| | D | | | | |
In Our Example last grp for 1115 person is D so when you start comparing
You have to compare as:
- D – C -à since groups are different it should be count as one 1
- D –B -à since groups are different it should be count as one 1
- D – B -à even though groups are different , but since this change has been counted once ,, so tthis time it should be ignored
- D – D à since groups are same so it shouldn’t be count at all.
D – D same for this one .
For Pivot Expression :
if([Plan GRP]=[Previous Plan GRP],count(distinct( [Person ID])),0)
but it doesn’t display output as desired
