How to create ranges

[canmarroig]

Hi, i've an excel table like this:

customer	prod a	prod b	prod c	prod d
a	100	3	2	0
b	1	2000	0	0
c	200	800	300	1000
d	50	23	400	51
e	23	25	50	0
f	11	44	56	11

i need to create in my frontend some ranges: how many customers have

• 0-500
• 501-1000
• 1001-2000

for each product. My output should be:

range	product a	product b	product c	product d
0-500	6	4	6	5
501-1000	0	1	0	1
1001-2000	0	1	0	0

Can you help me?

Tks

[MK_QSL · Accepted solution]

Data:
CrossTable(Product, Value)
Load * Inline
[
  customer, prod a, prod b, prod c, prod d
  a, 100, 3, 2, 0
  b, 1, 2000, 0, 0
  c, 200, 800, 300, 1000
  d, 50, 23, 400, 51
  e, 23, 25, 50, 0
  f, 11, 44, 56, 11
];


Left Join (Data)
Load
  customer,
  Product,
  If(Value >= 0 and Value <= 500, '0-500',
  If(Value > 500 and Value <= 1000, '501-1000',
  If(Value > 1000 and Value <= 2000, '1001-2000'))) as ValueFlag
Resident Data;

Now Create a Pivot Table

Dimensions

ValueFlag

Product

Expression

COUNT(ValueFlag)

[canmarroig]

Yess, tks!