3 Replies Latest reply: Mar 31, 2016 1:21 PM by pavan kumar RSS

    Compliance metric

    Colin Gardner

      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?



        • Re: Compliance metric
          Liron Baram

          hi try this

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

            • Re: Compliance metric
              Colin Gardner

              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.

            • Re: Compliance metric
              pavan kumar

              i think it works, liron is correct

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