Last week I was tasked with developing a dashboard to show how well an employee was performing over time relative to their peers across many metrics. Bringing all of these concepts into a single chart took several iterations but I believe the result could be applicable to many people. In the sample screenshot, we can evaluate Employee D’s performance each quarter relative to several peer benchmarks.In Q2 and Q3, D performed below the worst peer group, but in Q4 they improved. This is based on random data, but this type of analysis is highly valuable for managers to understand and coach employees appropriately.

After many months of building in Qlik, I was able to incorporate several of the complexities available in Qlik to create this chart. It uses the Aggr function to separate the data into appropriate sub-sets to be processed. Set analysis allows for evaluation of the entire set of employees for the percentile (fractile) calculation.

Development hit one road block where I was attempting to incorporate the time dimension. This was solved by including the exact type of time (month, quarter, or year) into the Aggr function.

Here is the recipe:

Variable to create many percentiles:

eFractile = Fractile({<Employee={*}>} Aggr(Sum({<Employee={*}>} Sales), Employee, Quarter),$1)

Chart – Combo Chart

Dimension: Quarter

Measure 1 [Bar Type]: Sum(Sales)

Measure 2-n [Line Type]: $(eFractile(0.25))

https://charts.qlikcloud.com/5839fa596000fbff00d4d1ce/chart.html