Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
See attached example
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
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
Nicole has simple dimension and complex expression.
I have complex dimension, and simple expression.
Some choice...
Thank you Nicole! That was just what I was looking for.
Thank you all for the helpfull answers!!