count if

[#1]

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

[er_mohit]

see the attached file

a:

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;

[#3 · Accepted solution]

hi

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)

[#4]

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 🙂