Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Nicolai_Mads
Partner - Contributor II
Partner - Contributor II

Sum Aggr in set analysis

Hi, 

I have been asked to visualize how many times one of our Employees sell to specific Business Area for more than 200.000$. 

So I basically want to sum sales amount and aggregate by business area and by employee.   

So far I have written the following script to count how many time a specific employee obtain this goal: 

if(aggr(sum(Sales Amount]), BusinessAreaID, [EmployeeID]) >= 200000,
count( distinct BusinessAreaID), 0). 

This in turn return me the following table: 

EmployeeID Business Area ID Sales Amount Measure
1 100 216.500 1
1 110 105.589 0
1 120 6.250 0
1 150 5.950 0
1 160 40.000 0
1 170 0 0
1 180 157.780 0
1 190 1.175 0
1 200 221.773 1
1 210 0 0
1 220 17.270 0

 

What I am looking is however a table summing up the measure pr employee so the table should look like this: 

EmployeeID New Measure
1 2
Labels (4)
1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

If you make a chart with EmployeeID as dimension, you should be able to use the following as measure

Sum(Aggr(If(Sum(Sales Amount])>200000,1), BusinessAreaID, [EmployeeID] ))

View solution in original post

2 Replies
hic
Former Employee
Former Employee

If you make a chart with EmployeeID as dimension, you should be able to use the following as measure

Sum(Aggr(If(Sum(Sales Amount])>200000,1), BusinessAreaID, [EmployeeID] ))

Nicolai_Mads
Partner - Contributor II
Partner - Contributor II
Author

Thanks for the swift reply Henric - your solution works!