5 Replies Latest reply: Feb 27, 2018 2:26 PM by Pablo Labbe RSS

    Dynamic Pareto / ABC (with a twist)

    Jens Kuelpmann

      Hello all,

       

      I am trying to come up with a formula in Sense that gives the Pareto- / ABC-class of orders as a dimension. While something like


      If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.8, 'A', If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.95, 'B', 'C'))


      works well as a measure when the orders are there as a dimension and I can sort them there, I am looking for a way to integrate the sorted order Dimension into the ABC-class (or something similar?). Showing individual orders themselves as dimension does not make sense because there can be thousands of them.


      I have tried different combinations of Aggr() but the sorting does not seem to work, so my results so far have been wrong.

       

      Example:

       

      Aggr(If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.8, 'A', If(RangeSum(Above(Sum(Meas1), 0, RowNo())) / Sum(TOTAL Meas1) <= 0.95, 'B', 'C')), (Dim1, (Sum(Meas1), DESCENDING)))


      I suspect I need more / nested Aggr() functions or to put them in different places?


      Any help would be greatly appreciated -- Jens