Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

8 Replies
chematos
Specialist II
Specialist II

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

Not applicable
Author

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

chematos
Specialist II
Specialist II

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


Not applicable
Author

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

chematos
Specialist II
Specialist II

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?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

chematos
Specialist II
Specialist II

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