Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
spcon2016
New Contributor

ABC distribution

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.

Community Browser