How to do an analysis that shows how many customers are in some range of total revenue

[#1]

Hi Experts,

I have a table like the following:

Customer	Value
A	100
B	20
B	50
C	150
A	200
D	800
D	400
C	200

So, I´d like to make an analysis that shows how many customers are in some range of total revenue. It would be someting like:

Range	No of Customers
0 to 100	1 (just B=>20+50=70)
101 to 1000	2 (A=>100+200=300 and C=>150+200=350)
1001+	1 (just D=>800+400=1200)

I tried to use set analysis, class, but couldn't figure out it.

[Nicole-Smith · Accepted solution]

Dimension:

=ValueList('0 to 100', '101 to 1000', '1000+')

Expression:

pick(match(ValueList('0 to 100', '101 to 1000', '1000+'), '0 to 100', '101 to 1000', '1000+'),

    count({<Customer={'=sum(Value)>=0'}*{'=sum(Value)<=100'}>}distinct Customer),

    count({<Customer={'=sum(Value)>100'}*{'=sum(Value)<=1000'}>}distinct Customer),

    count({<Customer={'=sum(Value)>1000'}>}distinct Customer)

    )

I've also attached a working example.

[Anonymous]

See attached example

[brindlogcool]

Also you can create the calculated dimension as

=If(aggr( sum(Value),Customer )<=100,'0 to 100' ,

If(aggr( sum(Value),Customer )>100 and aggr( sum(Value),Customer )<=1000,'101 to 1000',

'1001+'))

and the expression as count distinct customer

[Anonymous]

Leo

Does the attached help you ?

I sorted it in the load script like this :

Data :

LOAD * INLINE [

    Customer, Value

    A, 100

    B, 20

    B, 50

    C, 150

    A, 200

    D, 800

    D, 400

    C, 200

];

Ranged :

load

  Customer ,

  SumValue ,

  if ( SumValue <= 100 , '(a) 0 to100' ,

  if ( SumValue <= 1000 , '(b) 100to1000' , '(c) 1001+' ) ) as Range

;

LOAD

  Customer ,

  Sum(Value) as SumValue

resident Data

  group by Customer

  order by Customer

;

Best Regards,     Bill

[Anonymous]

Nicole has simple dimension and complex expression.

I have complex dimension, and simple expression.

Some choice...

[#7]

Thank you Nicole! That was just what I was looking for.

[#8]

Thank you all for the helpfull answers!!