8 Replies Latest reply: Jun 5, 2012 8:50 AM by JOSE MARIA TOS MIÑANA RSS

    Set Analysis

    vasofabrici

      Hi All,

       

      I have next problem with SET Analysis .

       

      I wanted to display a count of  Employees, if the CRU% for the employee is lower than 85 %. I applied following statement in expression:

       

      if(Sum({<UsrID=>}(CRUH))/Sum({<UsrID=>}( ESS_PLAN))<0.85, count(distinct UsrID),''

       

      It works quite well, but the problem is, if you want to see the count of employees with CRU below 85 % for the whole Team (highlited with blue), it doesn´t display count of employees, because the CRU of the whole team is higher than 85 % despite of, that the team contains employees with CRU under 85 % (highlited with red).

       

      How can I define a Set Analysis for the expression that I will see also the count of underutilized employees for the whole team?

       

      Thank you very much in advance.

       

      Vaso

       

      SKTeamID

      old/new

      EmployeeID

      CRU%

      Unused cap.

      FC1 fullfilment

      Count of Employees below 85%

      FBC-England

      Total

       

      87,79%

      418,1

      80,92%

       

      FBC-England

      old

      1

      98,87%

      1,3

      87,32%

       

      FBC-England

      old

      2

      94,64%

      6,0

      107,14%

       

      FBC-England

      old

      3

      87,89%

      15,5

      93,75%

       

      FBC-England

      old

      4

      90,18%

      11,0

      100,00%

       

      FBC-England

      old

      5

      96,88%

      4,5

      88,89%

       

      FBC-England

      old

      6

      100,16%

      -0,3

      84,21%

       

      FBC-England

      old

      7

      86,94%

      17,5

      83,58%

       

      FBC-England

      old

      8

      94,90%

      7,8

      68,42%

       

      FBC-England

      old

      9

      113,16%

      -20,0

      70,83%

       

      FBC-England

      old

      10

      102,94%

      -4,0

      88,24%

       

      FBC-England

      old

      11

      96,88%

      4,5

      83,33%

       

      FBC-England

      old

      12

      71,88%

      27,0

      0,83%

      1

      FBC-England

      old

      13

      93,38%

      9,0

      88,24%

       

      FBC-England

      old

      14

      100,39%

      -0,3

      200,00%

       

      FBC-England

      old

      15

      84,19%

      21,5

      94,12%

      1

      FBC-England

      old

      16

      89,80%

      15,5

      89,47%

       

      FBC-England

      old

      17

      84,05%

      24,3

      73,68%

      1

      FBC-England

      old

      18

      80,30%

      19,5

      72,73%

      1

      FBC-England

      old

      19

      97,43%

      3,5

      94,12%

       

      FBC-England

      old

      20

      75,00%

      36,0

      55,56%

      1

      FBC-England

      old

      21

      88,24%

      16,0

      88,24%

       

      FBC-England

      old

      22

      75,78%

      36,8

      72,25%

      1

      FBC-England

      old

      23

      95,82%

      5,0

      87,03%

       

      FBC-England

      old

      24

      91,80%

      10,5

      100,00%

       

      FBC-England

      old

      25

      88,16%

      18,0

      73,68%

       

      FBC-England

      old

      26

      2,94%

      132,0

      11,76%

      1

       

        • Set Analysis
          JOSE MARIA TOS MIÑANA

          Hi,

           

          If you want an expression that filters the count of employees, you need to try something like this:

           

          count({<CRU={'<=0.85'}>}UsrID)

           

          CRU must be calculated before as an expression or you could calculate it in the load script setting it as a field??

          I recommend to calculate the CRU in the load for each employee, so you will have a field with the CRU being easier to use with different levels os aggregation.

          Take care that the names I`m using are only an example, so the expression also.

          If you have any problem, please explain it with details.

           

          hope this helps

            • Set Analysis
              lukypuky

              Hi I have the same problem, I tried to use you expression:

              count({<CRU={'<=0.85'}>}UsrID)

              But it makes no difference.

              Here an example:

              PortfolioSKTeamIDold/newTLrelevancyPernrCRU HESS PlanCRU%Unused cap.F1 HoursFC1 fullfilmentNo. of underU
              Total 2,415.72,740.088.17%324.32,25582.30%-
              TeamTotal 2,415.72,740.088.17%324.32,25582.30%-
              TeamTEAMaTotal 2,415.72,740.088.17%324.32,25582.30%-
              TeamTEAMaoldTotal 2,415.72,740.088.17%324.32,25582.30%-
              TeamTEAMaoldemployyeNN2262.8280.093.84%17.218766.79%-
              TeamTEAMaoldemployyeNN3544.9550.898.94%5.952895.87%-
              TeamTEAMaoldemployyeNN4541.0632.085.60%91.047775.47%-
              TeamTEAMaoldemployyeNN5521.0621.383.86%100.349679.84%1
              TeamTEAMaoldemployyeNN6546.1656.083.24%109.948774.24%1
              TeamTEAMaoldemployyeNN70.00.0-0.080--

              As you can see the count of Underutilized employees within the team is 2 (employee NN5 and NN6), but on the level of a Team I will get no count of this two users, because the CRU% of the whole team is above the level of 85%. How could I solve this issue and get the count of 2 underutilized employees on the Team level?

              Thanks

                • Set Analysis
                  JOSE MARIA TOS MIÑANA

                  Hi, I think that you could use the Dimensionality() on a pivot table to make different calculations depending on the level of grouping.

                   

                  DImensionality() returns the number of dimensions that the table is opened so Dimensionality() of TLrelevancy is 4, Pernr is 5....

                  See this:

                  If(Dimensionality()=>4, count({<CRU={'<=0.85'}>}UsrID), if(Dimensionality()<=2,count(UsrID) )

                   

                  I hope this is what you´re looking for.

                   

                  Regards


                    • Set Analysis
                      lukypuky

                      Hi it still doesn't work. Do you have any ideas why it still doesn't work? I have used following expression.

                      If(Dimensionality()>=4, count({<CRU%={'<=0.85'}>} distinct Pernr), if(Dimensionality()<=2,count(distinct Pernr) ))

                       

                       

                       

                       

                       

                       

                      Thanks

                        • Set Analysis
                          JOSE MARIA TOS MIÑANA

                          when your table is open by  TLrelevancy, the count({<CRU%={'<=0.85'}>} distinct Pernr) must be 3 (NN5,NN6,NN7) but when the dimensionality() is 2(group by SKTeamID) your count may be all the employees.

                           

                          you could try with:

                          If(Dimensionality()>=4, count({<CRU%={'<=0.85'}>} distinct Pernr), count(distinct Pernr) ))

                           

                          That is the result you want, isn´t it? To make it clear.

                           

                          Are you sure that CRU%'s values are like 0.XX ??May be is 85 instead 0.85 or 0,85 instead 0.85 ??

                          What´s not working? Dimensionality or the values you get?

                           

                          Have you make it in a pivot table and have you expansed and contract the dimensions?

                        • Set Analysis
                          lukypuky

                           

                          Hi I've got the same result with both of the following expression:

                           

                          IF(Dimensionality()>=4,(count({<CRU%={'<85'}>}distinct Pernr)),if(Dimensionality()<4,count(distinct Pernr),''))

                           

                           

                          IF(Dimensionality()>=4,(count({<CRU%={'<85'}>}distinct Pernr)),count(distinct Pernr))

                           

                          Result: (on the right, marked red, you can see the result I would like to have)

                          PortfolioSKTeamIDold/newTLrelevancyPernrCRU HESS PlanCRU%Unused cap.CRU noteF1 HoursFC1 fullfilmentNo. of underUWhat I want to see
                          Total 2,415.72,740.088.17%324.3 2,25582.30%52
                          TeamTotal 2,415.72,740.088.17%324.3 2,25582.30%52
                          TeamTeam ATotal 2,415.72,740.088.17%324.3 2,25582.30%52
                          TeamTeam AoldTotal 2,415.72,740.088.17%324.3 2,25582.30%52
                          TeamTeam AoldemployyeNN2262.8280.093.84%17.218766.79%1-
                          TeamTeam AoldemployyeNN3544.9550.898.94%5.952895.87%1-
                          TeamTeam AoldemployyeNN4541.0632.085.60%91.047775.47%1-
                          TeamTeam AoldemployyeNN5521.0621.383.86%100.349679.84%11
                          TeamTeam AoldemployyeNN6546.1656.083.24%109.948774.24%11
                          TeamTeam AoldemployyeNN70.00.0-0.080-0-

                           

                          I have checked the numbers it should be 0.85, but I have also tried with 85.

                           

                          For more understanding the CRU% expression is calculated as follows:

                           

                          sum

                           

                          ({<Note={'actual'}>}CRUH)/sum({<Note={'actual'}>}ESS_PLAN)

                           

                          It's created as a pivot table, but I'm not sure what you mean by "have you expansed and contract the dimensions".

                           

                           

                           

                          Thanks

                           

                          Lukas

                            • Set Analysis
                              JOSE MARIA TOS MIÑANA

                              In this case, you should only need

                              count({<CRU%={'<0.85'}>}distinct Pernr)

                               

                              If you have the Employee dimension extended, you will see 1 in No. of underU but if your table is opened by Team dimension, you will see 2 in No. of underU.

                               

                              Right now you are getting 5 because it´s counting all the employees when the table is not opened by employee. I understood that were you want.

                               

                              Expanse and contract a pivot table is to make clik in the plus sign '+' inside de table to expand or '-' to contract the dimension, that is what you give different groupings.

                               

                              Regards

                      • Set Analysis
                        Oleg Troyansky

                        Try something like this:

                         

                        count( {<IsrID={"Sum(CRUH)/Sum( ESS_PLAN)<0.85"}>} distinct UsrID)

                         

                        you are counting those Users that satisfy the condition 0 the calculated percentage is less then 0.85.

                         

                        I don't guarantee the exact syntax...

                         

                        cheers,

                         

                        Oleg