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.
