Tag: data analysis

Percentile Analysis – Qlik Sense

Leave a comment

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

Intentional Data Analysis: “Know before you go”

Leave a comment

Lean Analytics – Ken Norton (Google Ventures)

Watch this video. It is a fundamental staple from my self-guided education over the past year. After learning from Ken’s excellent presentation, I was able to think about how to properly design my data analysis so that every metric that I collected, analyzed, and visualized would have some actionable piece of information realized. The beautiful business term of INSIGHT!

A personal example I made Excel macros at work as a majority of my work function involved formatting and thus was ripe for automation. I packaged these macros into an add-in that allowed the rest of my team to easily load the macros into their instance of Excel and click buttons. After watching “Lean Analytics”, I made a call that recorded the main metrics I was interested in: the computer name of whoever was using the add-in and what macro they were running. By collecting this information I was able to tell my boss that I was saving cumulative about a third of a team member’s time. I can also see which macros have had the most effect. By recording this information I have been able to focus my attention on where I can have the most impact.

The ability to gain these insights is limited by creativity during both analysis AND planning. To make life easier, think about creating your databases, tables, and other data structures with the intent that you are going to query for specific, actionable information. The more time you spend properly designing your data storage will pay off exponentially during the rest of the business’ life. If you don’t know about data storage best practices, you should google them.

Photo Source: Simran Jindal