Calculating variance on total of two columns

[#1]

Hi,

I need to calculate the Training compliance of a certain Business.  In a straight table I have the calculated the required training which is obtained from a list of required courses per individual (count[Training Required]) and completed Training, which is calculated in a similar fashion.

For the variance, I have taken Completed / Required * 100, which calculates the data correctly, but the total is not correct. 

In the below example, there are 6 types of courses that employees have to go on, but it is distributed among employees, which is where the total of 100 comes from.  If i use the expression total for the variance calculation, it takes the 6 types instead of the number of courses for all employees.

DIVISION	Engagement Month	EE No	Required Training	Completed	Variance
			100	12	200
Division A	Jan-14	62913	2	3	150
Division A	Jan-14	57856	2	1	50
Division A	Jan-14	66144	2	1	50
Division A	Jan-14	62053	2	1	50
Division A	Jan-14	46470	2	1	50

Please assist if possible. 

Thanks,

Naomi

[Nicole-Smith]

It would be easier to help you if you post an example file: Preparing examples for Upload - Reduction and Data Scrambling

[#3]

Naomi Kruger

I'm assuming this could be solution.... try to use AGGR() in you REQUIRED and COMPLETED calc

eg :   Count( Aggr(training_Count, Month, Division))

[stevedark]

It is Aggr that you will need as Surya suggests.  The expressions will be something like:

=sum(Aggr(count([Training Required]), [EE No], DIVISION, [Engagement Month]))

and

=sum(Aggr(count([Completed Training]), [EE No], DIVISION, [Engagement Month]))

then finally

=sum(Aggr(count([Completed Training]), [EE No], DIVISION, [Engagement Month]))

/ sum(Aggr(count([Training Required]), [EE No], DIVISION, [Engagement Month]))

If you Google it you can find information on Aggr in QlikView, but essentially you are getting the count for each combination of the dimensions and then adding them up with the sum.  On each row of the table you are just adding up one value - but on the total it should give you the right answer.

You may need to play with the parameters a little - but hopefully that will get you nearer.  Make sure you have Expression Totals rather than Sum Of Rows on the Total Mode as well.

Hope that helps.

Steve