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: 
salmany
Partner - Contributor II
Partner - Contributor II

How to calculate average based on multiple dimensions

Hi,

I would really appreciate for the guidance on the following scenario.

I have 4 columns Emp No, Designation, Salary and Year. And i want to calculate the average salary by designation for each year. But when I display Avg(Salary) measure against Designation and Year dimensions in a table on front end, I am getting same average salary value against all years for a particular designation.

Attached is the sample data with desired and current results i am getting.

Post 1.PNG

Thanks.

6 Replies
Lisa_P
Employee
Employee

This is not normal behaviour, what details are you not telling us?

salmany
Partner - Contributor II
Partner - Contributor II
Author

I have tried to explain the scenario in detail and provided the completed information. Appreciate if you can point me in the right direction.

Thanks

Frank_Hartmann
Master II
Master II

1:
LOAD * INLINE [
    Designation, Salary, Year, Emp.Code
    Ayah, 17572, 2020, 10
    Ayah, 19218, 2020, 11
    Ayah, 15135, 2019, 10
    Ayah, 16067, 2019, 11
    Ayah, 14135, 2018, 10
    Ayah, 16067, 2018, 11
    Cleaner, 17572, 2020, 7
    Cleaner, 17572, 2020, 31
    Cleaner, 15440, 2019, 7
    Cleaner, 15440, 2019, 31
    Cleaner, 15750, 2019, 35
    Cleaner, 14440, 2018, 31
    Cleaner, 15750, 2018, 35
    Cleaner, 15440, 2018, 46
];

left join

load Designation,Year, sum(Salary)/count(Designation) as avg Resident 1 Group by Designation,Year;
salmany
Partner - Contributor II
Partner - Contributor II
Author

Thanks Frank.

 

But cannot we achieve the same result from front end  (i.e. app editor) instead of writing script in data loader?

Frank_Hartmann
Master II
Master II

try this as expression:

sum(Salary)/count(Total<Year,Designation>Salary)
salmany
Partner - Contributor II
Partner - Contributor II
Author

Thanks everyone.

I got the desired output.