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

    Sum of Aggregation only for specific IDs

    Nelly Lukina

      Hi,

      Please help find the answer to the following:

      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)

      ))

      Please, Help

        • Re: Sum of Aggregation only for specific IDs
          Gysbert Wassenaar

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

            • Re: Sum of Aggregation only for specific IDs
              Nelly Lukina

              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
                  Jakub Michalik

                  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.