0 Replies Latest reply on Dec 6, 2018 2:56 PM by Simon Zhang

# WINDOW_PERCENTILE help with an aggregate measure (10.5)

Hello there!

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 10 10 80 8 10 Primary 7 6 33 5.5 16 Primary 8 24 57 2.375 40 Primary 1 22 50 2.272727273 62 Primary 3 66 120 1.818181818 128 Secondary 5 100 150 1.5 228 Tertiary 4 88 130 1.477272727 316 Quarternary 2 55 80 1.454545455 371 Quarternary 6 33 15 0.454545455 404 Quinary 9 56 23 0.410714286 460 Quinary 11 1 0 0 461 Quinary 12 2 0 0 463 Quinary 13 3 0 0 466 Quinary 14 2 0 0 468 Quinary 15 1 0 0 469 Quinary 16 2 0 0 471 Quinary 17 3 0 0 474 Quinary