topic Re: Sum of Aggregation only for specific IDs in QlikView

Sum of Aggregation only for specific IDs

Thu, 15 Dec 2016 08:32:14 GMT

Hi,

Please help find the answer to the following:

my data is the following:

Department	EmployeeID	SpecialEmployeeID	Salary
12	1	-	100
12	2	-	200
12	3	3	300
12	4	-	400
12	5	5	500
12	6	6	100

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)

))

Please, Help

Re: Sum of Aggregation only for specific IDs

Gysbert_Wassenaar — Thu, 15 Dec 2016 08:37:38 GMT

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

Re: Sum of Aggregation only for specific IDs

Thu, 15 Dec 2016 09:32:54 GMT

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

kuba_michalik — Thu, 15 Dec 2016 14:18:30 GMT

Is this your requirement?

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.