Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
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
Please close the thread by marking correct answer & give likes if you like the post.

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
Please close the thread by marking correct answer & give likes if you like the 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! 🙂