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)
