0 Replies Latest reply: Aug 14, 2016 5:38 PM by archana handu RSS

    help with Pivot

    archana handu

       

      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:

       

      1. D – C    -à since groups are different it should be count as one 1
      2. D –B     -à since groups are different it should be count as one 1
      3. D – B    -à even though  groups are different , but since this change has been counted once ,, so tthis time it should be ignored
      4. 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