Need help: Counting customers who's monthly totals exceed some thresholds

[#1]

Hi guys,

Need your help badly. Would you please suggest me a solution to the following problem:

I have a certain number of customers (CUST#), for each customer I have pre-aggregated transactions (a customer may have several records for any month).

CUST#	MONTH	TRANS.VALUE	TRANS.QTY	TRANS.VALUE*QTY
99	Feb.10	25	1	25

I need to get (for each month) the number of customers that have total transactions' value >= 100 and those that have less than 100 (<100).

Like this (the figures are wrong, just to show the template):

MONTH	<100	>=100
Jan.10	2	3
Feb.10	5	3
Mar.10		9
Apr.10	4	7
May.10	3	2
Jun.10	3	8
Jul.10	2	5
Aug.10	1	10
Sep.10	4	3
Oct.10	2	7
Nov.10	3	2
Dec.10	1	15

Thanks a lot in advance.

Dmitry

Here is the sample:

CUST#	MONTH	TRANS.VALUE	TRANS.QTY	TRANS.VALUE*QTY
99	Feb.10	25	1	25
99	Sep.10	50	1	50
99	Sep.10	50	1	50
99	Oct.10	25	4	100
99	Jun.10	50	2	100
99	Sep.10	50	1	50
98	Dec.10	100	1	100
88	Nov.10	70	1	70
88	Jun.10	70	1	70
88	Mar.10	70	2	140
88	Nov.10	25	1	25
88	Oct.10	80	1	80
88	Feb.10	70	1	70
88	Mar.10	100	1	100
88	Aug.10	70	1	70
88	Jul.10	70	1	70
88	Dec.10	100	1	100
74	Jan.10	50	2	100
74	Apr.10	50	2	100
74	Jun.10	25	2	50
74	Jul.10	50	1	50
74	Mar.10	50	2	100
74	Nov.10	50	2	100
74	Oct.10	50	1	50
74	Oct.10	50	1	50
74	Mar.10	50	2	100
74	Jul.10	50	2	100
74	Apr.10	50	1	50
74	Feb.10	50	1	50
74	Dec.10	50	1	50
74	Dec.10	50	2	100
60	May.10	50	1	50
60	Aug.10	50	2	100
60	Apr.10	50	2	100
60	Oct.10	50	1	50
60	Jan.10	50	1	50
60	Apr.10	50	2	100
60	Sep.10	50	1	50
60	Jul.10	50	2	100
60	Oct.10	50	2	100
60	Dec.10	70	1	70
60	Nov.10	70	1	70
60	Jun.10	70	1	70
60	Jun.10	70	2	140
60	Dec.10	70	1	70
55	Apr.10	70	1	70
55	Aug.10	25	1	25
55	Aug.10	25	2	50
55	Aug.10	50	2	100
55	Jun.10	70	2	140
45	Sep.10	25	1	25
45	Aug.10	25	3	75
45	Dec.10	50	1	50
45	Aug.10	50	2	100
45	Apr.10	50	2	100
45	Dec.10	50	1	50
45	Dec.10	50	3	150
45	Jun.10	25	2	50
45	May.10	50	1	50
45	May.10	50	1	50
29	Oct.10	50	1	50
29	Jan.10	50	1	50
29	Jul.10	50	1	50
29	Mar.10	50	1	50
29	Mar.10	50	1	50
29	Dec.10	50	1	50
29	Sep.10	50	1	50
29	Aug.10	50	1	50
29	Apr.10	50	1	50
29	Aug.10	50	2	100
29	Oct.10	50	1	50
29	Dec.10	50	2	100
29	Feb.10	50	1	50
29	Dec.10	50	1	50
29	Mar.10	50	1	50
29	Aug.10	50	2	100
21	Oct.10	50	1	50
21	Jul.10	100	1	100
21	Apr.10	100	1	100
21	Dec.10	100	1	100
21	Apr.10	100	1	100
21	Jul.10	100	1	100
21	Jan.10	100	1	100
21	Feb.10	100	1	100
19	Aug.10	100	1	100
19	Feb.10	20	1	20
13	Jun.10	50	4	200
13	Dec.10	25	3	75
13	Jun.10	25	2	50
13	Jun.10	50	2	100
13	Apr.10	50	1	50
13	Nov.10	50	2	100
1	Mar.10	70	1	70
1	Mar.10	50	1	50
1	May.10	70	1	70
1	Jun.10	100	1	100
1	Jan.10	70	2	140
1	Feb.10	70	1	70
1	May.10	10	1	10
1	Dec.10	70	1	70
1	Apr.10	70	1	70
1	Sep.10	50	1	50
1	Feb.10	70	1	70
1	Dec.10	70	2	140

[swuehl · Accepted solution]

Hi Excelgod,

you could use expressions like

=count( aggr(nodistinct if( sum( [TRANS.VALUE*QTY]) <100, CUST#), CUST#, MONTH))

for < 100 count and

=count( aggr(nodistinct if( sum( [TRANS.VALUE*QTY]) >=100, CUST#), CUST#,MONTH))

in a table chart with dimension MONTH.

See also attached.

Stefan

[#3]

Hey Stefan. Thanks a lot Will check your solution immediately.

[#4]

brilliant. works perfectly. you're genius

p.s. just changed "nondisctinct" to "distinct" since that's what I actually needed (that summary table from Excel shows non-disctinct just because there is no count distinct in Excel pivots.)