8 Replies Latest reply: Jun 5, 2012 8:50 AM by JOSE MARIA TOS

# Set Analysis

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

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

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:

 Portfolio SKTeamID old/new TLrelevancy Pernr CRU H ESS Plan CRU% Unused cap. F1 Hours FC1 fullfilment No. of underU Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% - Team Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% - Team TEAMa Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% - Team TEAMa old Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% - Team TEAMa old employye NN2 262.8 280.0 93.84% 17.2 187 66.79% - Team TEAMa old employye NN3 544.9 550.8 98.94% 5.9 528 95.87% - Team TEAMa old employye NN4 541.0 632.0 85.60% 91.0 477 75.47% - Team TEAMa old employye NN5 521.0 621.3 83.86% 100.3 496 79.84% 1 Team TEAMa old employye NN6 546.1 656.0 83.24% 109.9 487 74.24% 1 Team TEAMa old employye NN7 0.0 0.0 - 0.0 80 - -

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

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

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

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

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)

 Portfolio SKTeamID old/new TLrelevancy Pernr CRU H ESS Plan CRU% Unused cap. CRU note F1 Hours FC1 fullfilment No. of underU What I want to see Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% 5 2 Team Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% 5 2 Team Team A Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% 5 2 Team Team A old Total 2,415.7 2,740.0 88.17% 324.3 2,255 82.30% 5 2 Team Team A old employye NN2 262.8 280.0 93.84% 17.2 187 66.79% 1 - Team Team A old employye NN3 544.9 550.8 98.94% 5.9 528 95.87% 1 - Team Team A old employye NN4 541.0 632.0 85.60% 91.0 477 75.47% 1 - Team Team A old employye NN5 521.0 621.3 83.86% 100.3 496 79.84% 1 1 Team Team A old employye NN6 546.1 656.0 83.24% 109.9 487 74.24% 1 1 Team Team A old employye NN7 0.0 0.0 - 0.0 80 - 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

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

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