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

    WINDOW_PERCENTILE help with an aggregate measure (10.5)

    Simon Zhang

      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

       

           

      SkillCalls DeliveredRevenueRevenue per Delivered CallCalls D AccDistr
      101080810Primary
      76335.516Primary
      824572.37540Primary
      122502.27272727362Primary
      3661201.818181818128Secondary
      51001501.5228Tertiary
      4881301.477272727316Quarternary
      255801.454545455371Quarternary
      633150.454545455404Quinary
      956230.410714286460Quinary
      11100461Quinary
      12200463Quinary
      13300466Quinary
      14200468Quinary
      15100469Quinary
      16200471Quinary
      17300474Quinary

       

      Please let me know if I can clarify my ask further.

       

      Thank you for your time!!

       

      Zhouyi Zhang  Richard Leeke