I'm mired on this task and would really appreciate some direction. I have some data relating to a call center. The metric I'm targeting is an aggregate one: Revenue per Delivered Call.
I'm looking to create a quintile distribution for a "Skill" dimension based on Revenue per Delivered Call. Each Skill has an aggregated Revenue per Delivered Call based on SUM(Revenue)/SUM(Calls Delivered) so there is one Revenue per Delivered Call per Skill.
I'm using window_percentile for this endeavor, however I have a bunch of Skills that have $0 Revenue that are showing up in the view. Since window_percentile is a table calc, Skill count is used as the basis for determining the quintile distribution instead of Revenue per Delivered Call. I still want those $0 Revenue skills to be considered in the quintile because they still have Calls associated with them.
I've attached some sample data as well as a sample workbook. Given the sample data, I would expect the boundaries for 20th, 40th, 60th, 80th percentile (based off of Call frequency as well as Revenue per Delivered Call [desc]) to be: 94.8 calls, 189.6, 284.4, 379.2
|Skill||Calls Delivered||Revenue||Revenue per Delivered Call||Calls D Acc||Distr|
Please let me know if I can clarify my ask further.
Thank you for your time!!