Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Compliance metric

I have a bunch of salesman data, and there is a KPI that a salesperson must have more than 30 sales, per day.

I want to create a chart that shows the number of days that this KPI is met, out of the total number of days they worked. For example, if John sells over 30 products each day for 25 out of 29 days, then the chart should show 86%

I have tried the following expression, with SalesPerson as my dimension:

=if(aggr(count(distinct SaleID),Salesperson,SaleDate)>30, count(SaleDate))) / count(SaleDate)

This isn't giving me the results that I'm hoping for. What should my aggr() function be to only count the days that a person meets their goal?

Thanks

1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Re: Compliance metric

hi try this

sum(aggr(if(count(distinct SaleID)>30,1,0),Salesperson,SaleDate))/ count(Distinct SaleDate)

3 Replies
lironbaram
Honored Contributor II

Re: Compliance metric

hi try this

sum(aggr(if(count(distinct SaleID)>30,1,0),Salesperson,SaleDate))/ count(Distinct SaleDate)

Not applicable

Re: Compliance metric

Thanks so much, that did the trick! I didn't think to do a sum(1) instead of trying to count the date.

Also, if I wanted to change the dimension to group the Salespersons into their respective Region, how could I best calculate the average Compliance% by Location?

Would I nest it into an Avg Aggr like the following:

avg(aggr(sum(aggr(if(count(distinct SaleID)>30,1,0),Salesperson,SaleDate))/ count(Distinct SaleDate), SalesPerson)


EDIT: Yes, that worked to nest the function to find the average across all Salespersons, by Location.

pandunallani
Contributor II

Re: Compliance metric

i think it works, liron is correct

sum(aggr(if(count(distinct SaleID)>30,1,0),Salesperson,SaleDate))/ count(Distinct SaleDate)

Community Browser