Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
spcon2016
Partner - Contributor III
Partner - Contributor III

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.

0 Replies