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

average of rows in a column

Hi all.
I have a table as this:
1 dimension: Year
3 expression: Employees, Cost, NewValue
YearEmployeesCostNewValue
20114130014400
20125270018000
20137680025200
I want calculate the colum NewValue. It is given: avarege of all the value in the column cost multiplied by each value of the column Employees.
14400 = (1300+2700+6800)/3 * 4
18000 = (1300+2700+6800)/3 * 5
25200 = (1300+2700+6800)/3 * 7

any ideas?
Thanks!
17 Replies
manideep78
Partner - Specialist
Partner - Specialist

Hello,

This should work perfect.

=Avg(Total Cost)*Employees

Please find attached qvw

Regards,

Manideep

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

Check with this

=$(=Sum(Cost)/ Count(Distinct Year))* Count(Distinct Employees)

Hope it helps

Not applicable
Author

Your solution works. But the field Employees and Cost are 2 expressions.
Employees = count(person)

Cost = sum(costs) / sum(count(person))

If I try

avg(all sum(costs) / sum(count(person))) * sum(count(person)

no work!

Not applicable
Author

Your solution works. But the field Employees and Cost are 2 expressions.
Employees = count(person)

Cost = sum(costs) / sum(count(person))

If I try

avg(all sum(costs) / sum(count(person))) * sum(count(person)

no work!

CELAMBARASAN
Partner - Champion
Partner - Champion

Did You tried with this?

=$(=Sum(costs) / Count(person)) * count(person)

Celambarasan

narender123
Specialist
Specialist

Hi,

Tis could help you.

manideep78
Partner - Specialist
Partner - Specialist

Here is the solution you need,

LOAD * INLINE [

    Year, Persons, Cost

    2011, 2, 650

    2012, 2, 1350

    2013, 5, 3400

    2011, 2, 650

    2012, 3, 1350

    2013, 2, 3400

];

Take Year as a Dimension in straight table and add following 4 expressions:

Employees: =Sum(Persons)

Actual Cost:=Sum(Cost)


AverageCost:=Aggr(Sum(Total Cost),Year)/3


Final:=AverageCost*Employees.

After this right click on properties-->Presentation tab--> Select AverageCost Column and select  Hide Column option.

You can also set No Totals option to every column.

Here is the Output Table


Year Employees Actual Cost Final
2011 4 1300 14400
2012 5 2700 18000
2013 7 6800 25200

Please find the attached qvw.

Regards

Manideep

Not applicable
Author

(Sum(TOTAL Cost)/Count(TOTAL Cost))*Employees