ABC distribution

spcon2016 — Tue, 30 May 2017 14:14:34 GMT

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)

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.

topic ABC distribution in QlikView

ABC distribution