0 Replies Latest reply: May 30, 2017 10:17 AM by Kerim Koc RSS

    ABC distribution

    Kerim Koc

      Hi colleagues,

       

      I am a little bit in trouble right now, because I have absolutly no idea how I could realize an ABC-distribution of my customers without raw data from the data source.

       

      Only the following information are available:

      - Sales

      - Customer_ID

      - distribution requirements (A-Customers: 60% of Sales, B-Customers: 30% and C-Customers: 10%)

       

       

      Well, with the following formula I was able to create a good looking diagram:

       

      A-Customers:

       

      IF(Rangesum(Above(SUM(if(YearStart(date(today())) <= MyDate AND MyDate <= date(today()) ,SALES)),0,RowNo()),CUSTOMER_ID)

      <

      SUM(TOTAL if(YearStart(date(today())) <= MyDate AND MyDate <= date(today()) AND , SALES*0.6)),Rangesum(Above(SUM(if(YearStart(date(today())) <= MyDate AND MyDate <= date(today()) AND ,SALES)),0,RowNo())))


       

      I would like to provide users following functions:

      - filter by A, B and C customers

      - how many A,B, and C customers we have.

       

      The filter-functionality is my biggest problem. I have no dimension like A,B,C, they are calculated within an expression.

       

      My question: Is it possible to define the formula above as A-Customer Dimension within the scripting-layer? As I mentioned I only have the Customer_ID and Sales information.

       

      Example:

      LOAD

      IF(Rangesum(Above(SUM(if(YearStart(date(today())) <= MyDate AND MyDate <= date(today()) ,SALES)),0,RowNo()),CUSTOMER_ID)

      <

      SUM(TOTAL if(YearStart(date(today())) <= MyDate AND MyDate <= date(today()) AND , SALES*0.6)),Rangesum(Above(SUM(if(YearStart(date(today())) <= MyDate AND MyDate <= date(today()) AND ,SALES)),0,RowNo())))

      AS [A-CUSTOMERS]

      ......

       

      At least, sorry for my short approach but as I mentioned I have absolutly no idea how to build up these functionalities.

       

      Thank you in advance and best regards.