How to calculate the percentage of counts

[#1]

Hi All,

I am new to QlikView and this is my first open question. Apologies if this is really basic.

I have a file with 3 columns , emp_ID , productive_Hours , available_Hours .

Utilization is calculated as productive_Hours / available_Hours ( i am able to do this in QlikView )

what I want to compute is how many employees have utilization less than 50% as a percentage of the total employees.

My input file has duplicates on Emp code as in case of E1. ( here utilization is calculated as sum of productive_Hours / sum of available_Hours)

I am not able to calculate "Ratio of Count of yes to the total count of records" ( in this case 4/9 or 44.44%)

Thanks for your help.

Regards

Rohit

Formulas used	Input 1	Input 2	input1 / input2	if( utilization < 50% then yes else no)
emp_ID	productive_Hours	available_Hours	Utilization	Deployable ?
E1	50	50	67%	no
E1	10	40
E2	85	90	94%	no
E3	20	100	20%	yes
E4	10	100	10%	yes
E5	0	70	0%	yes
E6	75	100	75%	no
E7	90	100	90%	no
E8	0	100	0%	yes
E9	50	50	100%	no

[nilapril2010]

Hi,

At the time of data loading, create a new column deployable As :-

If(Input1/Input2>=50,1,0) As Deployable

Then your two expression will be :-

Expression 1:- If(Deployable=1,'Yes','No')

Expression 2:- (Ration count)   Sum(Deployable)/Sum( Total Deployable)

Regards

Nilanjan

[effinty2112 · Accepted solution]

Hi Rohit,

This expression in a text box give you the answer:

=Num(Sum(Aggr(if(Sum(productive_Hours)/Sum(available_Hours) < 0.5,1),emp_ID))/Count(DISTINCT emp_ID),'0.00%')

44.44%

If you're new to QlikView it will be hard to follow. The tough bit is the part that calculates the number of emp_ID whose ratio of productive to available hours is under 0.5.

The Aggr function is very useful but takes a but of time to learn. It takes an expression followed by one or more dimensions and creates an in memory array a bit like a straight table. Because it creates an array not a number we need to put it inside an aggregation function such as Sum() to get a result.

Aggr(Sum(productive_Hours)/Sum(available_Hours),emp_ID)

Creates an in memory table like this:

emp_ID	Utilisation
E1	66.67%
E2	94.44%
E3	20.00%
E4	10.00%
E5	0.00%
E6	75.00%
E7	90.00%
E8	0.00%
E9	100.00%

Changing the formula as shown below returns an array of 1s:A 1 for each emp_ID with Utilisation < 0.5:

Aggr(if(Sum(productive_Hours)/Sum(available_Hours) < 0.5,1),emp_ID)

emp_ID	Utilisation
E1
E2
E3	1
E4	1
E5	1
E6
E7
E8	1
E9

Now we place the Aggr expression inside an aggregation function, a sum function that adds up the 1 s to give us our answer.

Sum(Aggr(if(Sum(productive_Hours)/Sum(available_Hours) < 0.5,1),emp_ID)) returns 4.

Kind regards

Andrew

[#4]

Thanks Andrew ,

Really helpful , solves my current problem and also opens up some thoughts on how to use inmemory array.

Regards

Rohit

[effinty2112]

Hi Rohit,

Glad to be of help. If you're satisfied that I've answered your question please mark as correct.

Thanks

Andrew

[#6]

Hi Andrew

Under the Actions button I could see a single option "Helpful", so I selected the same. Not sure how to mark your solution as Correct.

Regards

Rohit

[oknotsen]

Do you see a green star followed by the test "Correct Answer" on every post?

If you click on that, you flag that post as the Correct Answer.

You can only do this once per topic.

I will do it for you for this topic .

[#8]

Thanks Onno van

I was not able to see the Correct answer button to the replies to this post.

I am able to see it for replies for another question I posted.

This Qlik community is really helpful

Thanks again for your help.

Regards

Rohit