Compliance metric

[#1]

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

[lironbaram · Accepted solution]

hi try this

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

[#3]

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.

[Anonymous]

i think it works, liron is correct

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