Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
itdepartment
Contributor II
Contributor II

Calculate avg by dimension

Hi community! Very greatful if someone can help my with this situation.

In the example below, I try to obtain the correct average. This is my dataset:

LOAD * INLINE [
Employee, Month, %WorkingDay, Salary
A, jan, 50, 200
A, feb, 75, 200
B, jan, 100, 300
B, feb, 100, 300
C, jan, 100, 300
];

 

I expect to obtain the correct value of Salary average (200+300+300)/3 = 266,66. In my expression, I use set analysis and the result make sense

itdepartment_0-1617034264531.png

When I add %WorkingDay dimension, one value of Salary disappears on row 2 but the total result is still ok.

itdepartment_1-1617034479806.png

I would like to show the table with all data, like this:

Employee%WorkingDayavg(aggr(Salary, Employee))
  266,67
A50200,00
A75200,00
B100300,00
C100300,00

 

I try to modify set analysis expression adding %WorkingDay dimension, but the total result now is not correct.

itdepartment_2-1617034925776.png

Very appreciate with your answers!

1 Solution

Accepted Solutions
MayilVahanan

HI @itdepartment 

Try like below

If(Rowno() = 0, Avg(Aggr(Avg(Salary), Employee)), Avg(Salary))

Thanks & Regards,
Mayil Vahanan R

View solution in original post

2 Replies
MayilVahanan

HI @itdepartment 

Try like below

If(Rowno() = 0, Avg(Aggr(Avg(Salary), Employee)), Avg(Salary))

Thanks & Regards,
Mayil Vahanan R

View solution in original post

itdepartment
Contributor II
Contributor II
Author

Hi MayilVahanan, it works!

 

I made a change in your expression: RowNo(Total) instead of RowNo() because my object is a straight table. For pivot table RowNo() is ok.

 

Thanks for your answer! 🙂