topic count if in QlikView

count if

Mon, 04 Feb 2013 10:29:24 GMT

Dear Professionals,

I would like to create a pie chart, based on following content:

Customer	Revenue
A	100
B	200
C	1400
D	600
E	800
F	700
G	50
H	400

I tried to implement a formula, which counts all customers with revenues between 0 and 499, 500 and 999, 1000 and 1499.

The outcome of aforementioned table would be:

0 and 499 --> 4 (customers)

500 and 999 --> 3 (customers)

1000 and 1499 --> 1 (customer)

The formula probably is sth. with count(if(revenue>0... , but to be honest, I am a newbie in QlikView and my current knowledge is exhausted here 😞

Many thanks for your help and best regards, Mark

Re: count if

er_mohit — Mon, 04 Feb 2013 10:53:21 GMT

see the attached file

LOAD * INLINE [

Customer, Revenue

A, 100

B, 200

C, 1400

D, 600

E, 800

F, 700

G, 50

H, 400

];

load *,

RangeOnly(if(Revenue<499,Customer))as cust_500,

RangeOnly(if(Revenue>500 and Revenue<999,Customer))as cust_1000,

RangeOnly(if(Revenue>1000 and Revenue<1499,Customer))as cust_1499

Resident a;

Re: count if

Mon, 04 Feb 2013 12:15:47 GMT

LOAD customer,

revenue,

RangeOnly(if(revenue>0 and revenue<=499,customer))as c1,

RangeOnly(if(revenue>500 and revenue<=999,customer))as c2,

RangeOnly(if(revenue>1000 and revenue<=1499,customer))as c3

FROM

C:\Users\vishwaranjan\Desktop\COUNT.xlsx

(ooxml, embedded labels, table is Sheet1);

then on text object

count(c1),

count(c2),

count(c3)

Re: count if

Mon, 04 Feb 2013 15:42:27 GMT

Many thanks for your help!

I've got a short additional question ... is there a possibility to build a sum of all revenues within one group?

sum(c1) doesn't work ... would be too easy probably 🙂