Count IDs by Range

[microwin88x]

Hello,

I have a table with the following fields:

• Category: A - B - C
• ID: 001 - 002 - 003
• Type: Billing - Sales
• Amount: Amount Value

This is a sample of the table (please use the attached QVW):

CATEGORY	ID	TYPE	AMOUNT
A	1	Billing	5.000
A	1	Sales	10.000
A	2	Billing	10.000
A	2	Sales	20.000
A	3	Billing	15.000
B	11	Billing	1.000
B	11	Sales	2.000
B	12	Billing	2.000
B	12	Sales	4.000
B	13	Billing	3.000
C	26	Billing	5.000
C	26	Sales	15.000
C	27	Billing	10.000
C	27	Sales	25.000
C	28	Billing	15.000

Then I need to summarize the Amount field by Type = Billing (Column 3) and Type = Sales (Column 4) and calculate the % = Billing / Sales (Column 5):

CATEGORY	ID	BILLING	SALES	%
A	1	5000	10000	0,5
A	2	10000	20000	0,5
A	3	15000	30000	0,5
A	4	20000	40000	0,5
A	9	45000	45000	1
A	5	25000	50000	0,5
A	8	40000	50000	0,8
A	6	30000	60000	0,5
A	7	35000	70000	0,5
A	10	50000	80000	0,63
B	11	1000	2000	0,5
B	12	2000	4000	0,5
B	13	3000	5000	0,6
B	14	4000	8000	0,5
B	15	5000	10000	0,5
B	18	8000	17000	0,47
B	19	9000	20000	0,45
B	20	10000	21000	0,48
B	21	11000	25000	0,44
B	22	12000	26000	0,46
B	23	13000	28000	0,46
B	24	14000	30000	0,47
B	25	15000	40000	0,38
B	16	6000	110000	0,05
B	17	7000	130000	0,05
C	29	20000	40000	0,5
C	30	25000	50000	0,5
C	31	30000	60000	0,5
C	32	35000	70000	0,5
C	34	45000	75000	0,6
C	33	40000	80000	0,5
C	35	50000	100000	0,5
C	26	5000	15000	0,33
C	27	10000	25000	0,4
C	28	15000	35000	0,43
C	36	55000	120000	0,46
C	37	60000	130000	0,46
C	38	65000	150000	0,43
C	39	70000	180000	0,39
C	40	75000	190000	0,39
C	41	80000	200000	0,4
C	42	85000	210000	0,4
C	43	90000	250000	0,36
C	44	95000	300000	0,32
C	45	100000	400000	0,25

I need to count how many IDs I have in the following ranges (grouped by Category):

• >= 50%
• < 50% >=30%
• < 30% >= 20%
• < 20%

So this would be the final result:

CATEGORY	BILLING	SALES	%	# ID	>= 50%	< 50%  >=30%	< 30%  >= 20%	< 20%
A	275.000	455.000	60,44%	10	10	0	0	0
B	120.000	476.000	25,21%	15	5	8	0	2
C	1.050.000	2.680.000	39,18%	20	7	12	1	0
Total	1.445.000	3.611.000	40,02%	45	22	20	1	2

Is there any way to do that?

Thank you!!!

[andrey_krylov · Accepted solution]

Hi, you can try this solution

Sum( If( Aggr(SUM({< TYPE={'Billing'} >} AMOUNT) / SUM({< TYPE={'Sales'} >} AMOUNT) , ID) >= 0.5, 1) )

[MarcoWedel]

you could use a generic load:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

hope this helps

regards

Marco