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: 
jessbanaga
Contributor II
Contributor II

How to count customers are selling products more than the sales target?

Hi Guys,

I have a table with data looks like below. I want to display in a separate pivot table how many are meeting the targets per agent per region.

RegionAgentYearMonthSalesAmount
XA20204200
XA2020550000
XB20205100000
YA2020430000
YB2020420000
ZA20205200000
ZB20205300000
ZB20204500

 

A selection input box is made available to select the target.  50000, 100000, 200000

Using Pivot Table I want to display how may are meeting the sales target based on the selected variable value.

Selected Value : 50000

Condition: Total Sales should be >= 50000 to meet the target:

RegionAgentCount
X2
Y0
Z2
Total4

 

Select Value : 100000

Condition : Total Sales shoud be >= 100000

RegionAgentCount
X1
Y0
Z2
Total3

 

I tried Sum and Count function combinations in the expression but to no avail.

Any help is highly appreciated.

BR, jess

 

2 Solutions

Accepted Solutions
Or
MVP
MVP

What you need is some AGGR(). Code may need a little tweaking since I'm typing it in directly here and not into a Qlik engine...

Count(AGGR(Sum(SalesAmount),Region,Agent, Year, Month)>$(vSalesTarget))

View solution in original post

jessbanaga
Contributor II
Contributor II
Author

Hi,

Thanks for the help.

I tweaked it, and below is the one working to meet my requirements.

SUM(FABS(Aggr(DISTINCT SUM(SalesAmount), Region, Agent) > $(vSalesTarget)))

BR, jess

View solution in original post

2 Replies
Or
MVP
MVP

What you need is some AGGR(). Code may need a little tweaking since I'm typing it in directly here and not into a Qlik engine...

Count(AGGR(Sum(SalesAmount),Region,Agent, Year, Month)>$(vSalesTarget))

jessbanaga
Contributor II
Contributor II
Author

Hi,

Thanks for the help.

I tweaked it, and below is the one working to meet my requirements.

SUM(FABS(Aggr(DISTINCT SUM(SalesAmount), Region, Agent) > $(vSalesTarget)))

BR, jess