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

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

Thu, 06 Feb 2014 17:56:36 GMT

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.

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

Nicole-Smith — Thu, 06 Feb 2014 18:12:21 GMT

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.

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

Anonymous — Thu, 06 Feb 2014 18:13:38 GMT

See attached example

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

brindlogcool — Thu, 06 Feb 2014 18:13:50 GMT

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

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

Anonymous — Thu, 06 Feb 2014 18:15:26 GMT

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

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

Anonymous — Thu, 06 Feb 2014 18:16:15 GMT

Nicole has simple dimension and complex expression.

I have complex dimension, and simple expression.

Some choice...

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

Fri, 07 Feb 2014 19:14:04 GMT

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

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

Fri, 07 Feb 2014 19:15:34 GMT

Thank you all for the helpfull answers!!