Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
|
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
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
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
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
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?
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
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
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