topic Set Analysis in QlikView

Set Analysis

Fri, 25 May 2012 15:20:25 GMT

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

chematos — Fri, 25 May 2012 15:43:24 GMT

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

Mon, 04 Jun 2012 14:54:44 GMT

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

chematos — Mon, 04 Jun 2012 15:06:01 GMT

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

Mon, 04 Jun 2012 15:33:51 GMT

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

chematos — Mon, 04 Jun 2012 15:56:07 GMT

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

Oleg_Troyansky — Mon, 04 Jun 2012 16:57:35 GMT

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

Set Analysis

Tue, 05 Jun 2012 07:38:10 GMT

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

chematos — Tue, 05 Jun 2012 12:50:20 GMT

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