3 Replies Latest reply: Dec 15, 2016 9:18 AM by Jakub Michalik

# Sum of Aggregation only for specific IDs

Hi,

my data is the following:

DepartmentEmployeeIDSpecialEmployeeIDSalary
121-100
122-200
1233300
124-400
1255500
1266100

I need to create a text object with the following: if EmployeeID<>SpecialEmployeeID and Salary of EmployeeID is higher than 300 then sum of their salary

That is what i`ve created, but it doesn`t work

sum(if(

aggr( if (EmployeeID<>SpecialEmployeeID , sum(Salary)  ,EmployeeID)>300,

aggr( if (EmployeeID<>SpecialEmployeeID , sum(Salary)  ,EmployeeID)

))

• ###### Re: Sum of Aggregation only for specific IDs

Try this: sum({<EmployeeID={"=EmployeeID<>SpecialEmployeeID AND Salary>300"}>}Salary)

• ###### Re: Sum of Aggregation only for specific IDs

Great, it works very well for the following example, thank you very much. The thing is i have another layer of complexity.

I have 2 more columns RsvYear and RsvMonth and AgencyID. Each Employee receive salary for 1 month and year from couple of Agencies. For example - in Nov 2015 Employee number 1 will receive salary from agency 3 and Agency 4. As a result for each month and employee i need sum(salary). Qlikview returns error.

if  use your expression I need something like this:

sum({<EmployeeID={"=EmployeeID<>SpecialEmployeeID AND sum(if(RsvMonth=month(today(1))-1 and RsvYear=year(today(1)),Salary))>300"}>}

sum(if(RsvMonth=month(today(1))-1 and RsvYear=year(today(1)),Salary)))

• ###### Re: Sum of Aggregation only for specific IDs

Return sum of salaries for employees without SpecialEmployeeID that in the previous month (by RsvMonth/RsvYear) received salary (in total across all agencies) over 300

(I'm asking because I'm not sure if I'm parsing this right...)

If so, the formula would be something like this:

Sum({<EmployeeID={"=EmployeeID<>SpecialEmployeeID AND sum({<RsvMonth={""=RsvMonth=Month(Today())-1""}, RsvYear={""=RsvMonth=Year(Today())""}>}Salary) > 300"}>})

Ignoring the different agencies is the easy part, because you can use aggregation in search expressions (including in set analysis), the result will be aggregated by the search field (Employee ID in this case). The "previous month" part is more complicated, and notice that what I proposed is a hack job (what happens if it's January?). I'd add a sequential month id in the data (something like Year * 12 + Month), and use it instead.